Re: Single Byte values

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Nigel J. Andrews
> Sent: Thursday, April 03, 2003 4:02 PM
> To: Jason Hihn
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Single Byte values
...
> I say you should use char(1) or whatever if that's what your requirement
> is. I'm not sure on the storage details but I doubt using text
> type will save
> anything.
>
> I also suspect the 5 byte cost isn't just the data but column overhead as
> well. I think the person saying not to use it is really saying why limit
> yourself to 1 character when for similar cost you can get 1
...

4 bytes(stored string length) + 1 data in the case of char(1).

Well this is a key for an enumeration, there are only a handful of values,
but thousands of records. I could do it via CHECK(a='a' || a='b' || a='c')
BUT I much rather dump that enumeration off to a table so to add a letter
later only requires INSERT INTO _table VALUES('d');

Furthermore, storing to off to a table allows a description of user-friendly
views of the data. Join on the column and you get an English (or whatever
your language) description. (Incidentally, it's not bad to change the schema
to:
create table _table
a char(1),
lang char(2),
desc text
primary key(a));

Where you can support multiple languages.

Incententally the typical representation is a list or drop-down box. Now you
can populate it with what's in _table

add a REFERENCES _table(a), and you have an easily extensible system that
the can add values too as well.

> instead of 1, which would really come back and haunt you if you'd
> done your
> database to be char(1) and had been amassing data for years
> before the change.

It could, but as it stands, I only have 10 or so for an application that's
been around for 15 years, and additions are rare. Even so this makes them
trivially easy... The decision to use a packed type over a vector type
should lie with the DB designer.