Re: Newbee question "Types"

On Thu, Dec 05, 2002 at 06:07:44PM -0800, Josh Berkus wrote:
> 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.

With some ODBC apps, CHAR can behave in less-than-expected ways.

CREATE TABLE c (c CHAR(5));
INSERT INTO c VALUES ('a');

will put a 'a____' into the field. PG will let you find this with

SELECT * FROM c WHERE c='a';

because it handles the padding v. non-padding fine.

However, an ODBC app like Access won't find it w/a normal,
Access-mediated query. You have to specify WHERE c='a____' to find it.
*Unless* you're writing a pass-through query (which Access doesn't
touch, but hands directly to PostgreSQL). So you can get different
behavior w/o expecting it.

If your app might be used in different environments, I'd think before
using CHAR, even in places that might seem 'safe' or 'obvious'.

- J.
--

Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant