5 ms·
use VARCHAR because constraints are a good thing, and use CHAR if you are storing strings of a fixed length, because semantics are a good thing. The point abo
by zzzeek 13y ago
use VARCHAR because constraints are a good thing, and use CHAR if you are storing strings of a fixed length, because semantics are a good thing. The point about padding being wasteful for variable-length data in CHAR is moot because CHAR is for storing fixed-size strings like state codes.
As the PG docs say, there is virtually no performance difference at all between all three, so stick with standard practices.
- VladRussian2 13y ago> As the PG docs say, there is virtually no performance difference at all between all three Btw, there is a clear [though infrequent] case where using CHAR(X) vs. VARCHAR(X) may cause huge performance penalty with iron platter HDD. CHAR(X) will always contribute X to the row length (even if data value is Y < X, so VARCHAR(X) with such value would contribute Y) thus making it more probable to have your row TOAST-ed ( i.e. have parts of row stored separately in separate table) thus causing a noticeable (when only few rows are accessed) extra disk head lookup on non-cached data. Similar to C/LOB in-row limit exceeding on other databases.
- zzzeek 13y agolike I said, don't use CHAR for non-fixed-length data. Use it for short fixed-length strings.
- jeltz 13y ago> and use CHAR if you are storing strings of a fixed length, because semantics are a good thing The CHAR type is not what most people think it is. It is a blank padded string, not a fixed length one. Inserting 'a' into a CHAR(2) results in 'a ' being stored and retrieved from the database. You should always used VARCHAR or TEXT in PostgreSQL and never CHAR (at least I cannot think of a case when you would want it). CHAR is there for SQL standard compliance. Instead use one of these: field VARCHAR(2) CHECK (length(field) = 2) field VARCHAR CHECK (length(field) = 2) field TEXT CHECK (length(field) = 2) The rules for working with blank padded strings is in my personal experience a hell with non-obvious results when casting, sorting, comparing and other operations. EDIT: I can leave you with this little example. $ SELECT length('ab '::char(4) || 'cd '::char(4)); length -------- 4 (1 row)
- netfeed 13y agoWe're storing currency codes, and they're always 3 chars (EUR, USD and so on) so it would just be stupid to actually use VARCHAR and not CHAR for that
- deleted 13y ago[deleted]
- orf 13y agorepeatedly storing 3 chars to represent a currency code seems wasteful in itself. a 2 byte small integer would suffice, surely?
- masklinn 13y agoWell not stupid per-se, since the result's the same. But it would provide less information to the reader.
- jfb 13y agoCREATE DOMAIN currency_code ...
- jeltz 13y agoI do not see how either VARCHAR or CHAR would provide more or less information since both are variable size strings. One is space padded and one is not. If you care about semantics you should create a domain based on VARCHAR.
- zzzeek 13y agothe fact that one sees "CHAR" in the schema definition is self-documenting that this is a fixed length field (and CHAR is "fixed length", the size of the string you get back will always be N), that's what I mean by "semantics". E.g., "what does it mean that the developer used CHAR here and not a VARCHAR?" The CHECK constraint you illustrated earlier can just as well be placed on a CHAR (using trim() as well to adjust for padding). Then there's no chance of any blank padding issues either.
- Tobu 13y agoIs anything really fixed-length? ISO country codes had a 2-char standard that got upgraded to a 3-char one, and RFC 5646 language codes are variable length. Plan for an extensible API, and just make sure that you control what data ends up on those tables.
- brlewis 13y agoWhat are you referring to with ISO country codes? There were 2-char and 3-char options from the beginning, and AFAIK the 2-char option is still the widely-used one. http://en.wikipedia.org/wiki/ISO_3166-1 http://en.wikipedia.org/wiki/ISO_3166-1