4 September 2018
In addition to the familiar text types
TEXT, PostgreSQL has a type
CHAR. It’s little used… and that’s for a reason. It has some very unusual behaviors, which can be quite a surprise if you are not expecting them.
CHAR is a fixed-width type. When character data is stored in it, it’s padded out with spaces if it is not full length:
xof=# create table chars (c char(20)); CREATE TABLE xof=# insert into chars values('x'); INSERT 0 1 xof=# select * from chars; c ---------------------- x (1 row)
OK, that’s reasonable, right? But what is going on here?
xof=# select length(c) from chars; length -------- 1 (1 row) xof=# select substring(c from 8 for 1) = ' '::char(1) from chars; ?column? ---------- t (1 row) xof=# select substring(c from 8 for 1) = ' '::varchar(1) from chars; ?column? ---------- f (1 row) xof=# select length(substring(c from 8 for 1)) from chars; length -------- 0 (1 row) xof=# select c || 'y' from chars; ?column? ---------- xy (1 row)
CHAR, when actually used, first trims off all trailing spaces, then applies the operation. It is trying to simulate a variable-length type, for historic reasons. This can be quite surprising, since a supposedly fixed-length type suddenly starts behaving as if it were variable. Unless you are terribly nostalgic for punched cards,
CHAR is generally not what you want.
Is there ever a time to use
CHAR? Not really. If you have a single-character enumeration that can never be either ” or ‘ ‘ (a single space), it might be more logical to store it as
CHAR(1) rather than
VARCHAR, but any space savings will be minimal and highly dependent on the alignment of the surrounding items.
And for n > 1, just use
TEXT. (Remember that in PostgreSQL,
TEXT are stored the same way.)