4 ms·

FWIW, I am guessing it's a rare case that you can be confident that all strings you'll want to store are of a specific length, but cannot predict the set of cha

FWIW, I am guessing it's a rare case that you can be confident that all strings you'll want to store are of a specific length, but cannot predict the set of characters they may use. The use cases for CHAR instead of VARCHAR that I'm thinking of would be things like serial numbers. CHAR(1) seems even more specific; the only time I've ever seen that used is for some version of enumerations with mnemonic keys.

shawnz 8y ago
I don't think the problem is so much about predicting the encoding that will be used. Let's say you KNOW that non-ascii characters will be used in the field, but only very occasionally. That means the field has to be 4x as wide as it would have been otherwise even if non-ascii characters will almost never be used.
- Retra 8y ago
  If you knew the characters were ASCII, you wouldn't want unicode support in the first place. It's pure overhead.
  - kbenson 8y ago
    But you also wouldn't want char(n), because as the article notes: char(n) doesn't reject values that are too short, it just silently pads them with spaces. So there's no actual benefit over using text with a constraint that checks for the exact length. So even if you're using pure ASCII, you're supposedly better off just setting the character encoding appropriately and using a text or non-limited varchar field. That said, I thought the main benefit of a char field (and its padding) was that you could use it in select instances where is was useful to have specific length records. E.g. if all the other fields of the table are fixed length, every record can be fixed length if you use a char(n). I'm not sure if that's a benefit or not to modern databases anymore though.
    - bunderbunder 8y ago
      Well, it depends. Some RDBMS's don't store the string's length with each value for fixed-width character fields. So a fixed-length field may still be preferable if the space lost to padding amounts to less than the space lost to overhead. That distinction doesn't apply to PostgreSQL, because it stores a length along with the value even for fixed-with columns.
derefr 8y ago
> things like serial numbers In Postgres, if I had a truly massive number of alphabetic-textual serial numbers (such that I was worried about their impact on storage/memory size), I’d define a new TYPE, similar to the UUID type, where such serial numbers would be parsed into regular uint64s (BIGINTs) on input, and generated back to their textual serial-number representation on output.
mhd 8y ago
The problem is that even with serial numbers things aren't as fixed. You might have a different type of serial number, zip code, etc.. A check constraint can be expanded to do the appropriate length and formatting checks for most types. A strict length restriction just does one simple thing. Same with name and address fields. I've seen databases where they set it at an seemingly arbitrary number, because that's the maximum length their delivery service could handle (UPS etc.), but then they added another one with different constraints…

shawnz 8y ago

I don't think the problem is so much about predicting the encoding that will be used. Let's say you KNOW that non-ascii characters will be used in the field, but only very occasionally. That means the field has to be 4x as wide as it would have been otherwise even if non-ascii characters will almost never be used.

Retra 8y ago
If you knew the characters were ASCII, you wouldn't want unicode support in the first place. It's pure overhead.
- kbenson 8y ago
  But you also wouldn't want char(n), because as the article notes: char(n) doesn't reject values that are too short, it just silently pads them with spaces. So there's no actual benefit over using text with a constraint that checks for the exact length. So even if you're using pure ASCII, you're supposedly better off just setting the character encoding appropriately and using a text or non-limited varchar field. That said, I thought the main benefit of a char field (and its padding) was that you could use it in select instances where is was useful to have specific length records. E.g. if all the other fields of the table are fixed length, every record can be fixed length if you use a char(n). I'm not sure if that's a benefit or not to modern databases anymore though.
  - bunderbunder 8y ago
    Well, it depends. Some RDBMS's don't store the string's length with each value for fixed-width character fields. So a fixed-length field may still be preferable if the space lost to padding amounts to less than the space lost to overhead. That distinction doesn't apply to PostgreSQL, because it stores a length along with the value even for fixed-with columns.

derefr 8y ago

> things like serial numbers In Postgres, if I had a truly massive number of alphabetic-textual serial numbers (such that I was worried about their impact on storage/memory size), I’d define a new TYPE, similar to the UUID type, where such serial numbers would be parsed into regular uint64s (BIGINTs) on input, and generated back to their textual serial-number representation on output.

mhd 8y ago

The problem is that even with serial numbers things aren't as fixed. You might have a different type of serial number, zip code, etc.. A check constraint can be expanded to do the appropriate length and formatting checks for most types. A strict length restriction just does one simple thing. Same with name and address fields. I've seen databases where they set it at an seemingly arbitrary number, because that's the maximum length their delivery service could handle (UPS etc.), but then they added another one with different constraints…