Re: Newbee question "Types" - Mailing list pgsql-general

Joe, Bruce,

> > 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.
>
> I disagree on that one.  Char _forces_ a specific length, so it pads to
> the specified length.  For certain applications, this can be a benefit,
> and often documents that the length is exactly as specified, e.g.
> CHAR(2) for US state codes.

Oops.  Replied to the wrong list.

"no reason ever" was probably too strong.  A better statement would have been
"almost never a reason".

My viewpoint, which some other developers certainly seem to disagree with:

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, and more
efficiently, 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.   After all CHAR originated when databases were unable to
manage VARCHAR.

Bruce gives the example of State Codes, which is a classic example, and
something I did myself in the past.   However, I generally found myself
forced to expand the state code field; the abbreviation for some US
Territories is 4 characters, and some countries use 3 for provinces.   At
that point, I very much needed to use VARCHAR, since I don't want 'CA__' as
my state.

CHAR can be a reminder to you, the developer, in reading the schema, that you
are expecting a string of a precise length.     But I do not see it as an
effective or useful constraint on input in real applications.  A constraint
statement, like the above, is far more effective.

Now, if the parser treated CHAR differently, then that would be a good
argument to use it.   But Tom Lane has said several times on SQL and HACKERS
that CHAR and VARCHAR are treated the same by the parser.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco