12:00

CHAR: What is it good for?

4 September 2018

In addition to the familiar text types VARCHAR and 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.

First, 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 VARCHAR… or TEXT. (Remember that in PostgreSQL, VARCHAR and TEXT are stored the same way.)

There are 2 comments.