Joe,
> > For a Postgres-native application, there is no reason to ever use CHAR.
> > It does not perform any better, and can lead to significant annoyance.
>
> That may be over-stating it. CHAR allows a table designer to define a
> constraint on the field. If you know that the area code is always three
> digits, CHAR(3) might make the most sense because it enforces the
> business rule in the table definition. That is also a reason to use
> VARCHAR instead of TEXT: you can specify a limit on the length. The
> more constrained data are, the more correct they probably will be.
Not really. CHAR(3) does not enforce a 3-character value. It just enforces
that characters which are not entered get filled with spaces. For a real
constraint, you would want something like:
three_code VARCHAR(3) not null,
CONSTRAINT cns_three_code CHECK (three_code ~ '[A-Z]{3}')
While you certainly *could* use CHAR for the above, it makes no difference
whatsoever to Postgres; if the column *must* be exactly 3 characters, then
Postgres will treat CHAR and VARCHAR exactly the same.
Come to think of it, the above declaration could just as easily be done with
TEXT. I tend to limit my use of TEXT because it gives ODBC fits.
Now, I can vaguely imagine hypothetical situations where a developer would
want ' ' instead of NULL for a character field. However, I have never run
across one in my application development, except for compatibility with
legacy software.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
