Skip site navigation (1) Skip section navigation (2)

Re: [GENERAL] Newbee question "Types"

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Joe Tomcat <tomcat(at)mobile(dot)mp>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [GENERAL] Newbee question "Types"
Date: 2002-12-06 01:48:13
Message-ID: 200212051748.13291.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-novice
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


In response to

pgsql-novice by date

Next:From: Josh BerkusDate: 2002-12-06 02:07:44
Subject: Re: Newbee question "Types"
Previous:From: Bruce MomjianDate: 2002-12-06 01:47:08
Subject: Re: Newbee question "Types"

pgsql-general by date

Next:From: Josh BerkusDate: 2002-12-06 02:07:44
Subject: Re: Newbee question "Types"
Previous:From: Bruce MomjianDate: 2002-12-06 01:47:08
Subject: Re: Newbee question "Types"

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group