Re: [GENERAL] Newbee question "Types"

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