Re: Poor-man's enumeration type

On Tue, 26 Apr 2005 01:06:51 -0400, Tom Lane wrote
> Michael Glaesemann <grzm(at)myrealbox(dot)com> writes:
> > On Apr 26, 2005, at 11:52, Keith Worthington wrote:
> >> The type "char" (note the quotes) is different
> >> from char(1)in that it only uses one byte of
> >> storage. It is internally used in the system
> >> catalogs as a poor-man's enumeration type.
> >>
> >> Is this a SQL standard data type?
>
> > No.
>
> Check.
>
> >> Can I expect this type to be supported in PostgreSQL 'forever'?
>
> > I'm not in a position to say, but I do know that "It is internally used
> > in the system catalogs" and the system catalogs carry with them the
> > explicit warning that they may change--and have changed in the
> > past--between releases.
>
> It's unlikely that we'd get rid of "char", simply because it'd be
> more work to get rid of its uses in the system catalogs than it
> would be worth. But bear in mind that we do feel free to whack
> around behaviors that are not mandated by the SQL spec. As an
> example, there used to be arithmetic operators (!) on the "char"
> type, but we got rid of them because they caused great confusion.
>
> If you use "char" columns only to store and retrieve single ASCII
> characters, and don't expect any more operations on them than simple
> comparisons, I think you'll be fine for the foreseeable future.
>
> regards, tom lane

I honestly tried to check on the SQL standard but I was unable to find a copy
online. Can you suggest a location?

As I see it there are two goals. First is the issue of storage space and
second is the issue of compatability.

Since this discussion has been centered around single character storage for
purposes of enumeration the differences are really small.

"char" 1 byte
char(1) 5 bytes
smallint 2 bytes
varchar(1) 5 bytes

The "char" although the smallest gets a black mark for compatability reasons.

It seems to me if I am going to perform some sort of lookup or use a case
statement to translate the data I might as well go with a smallint and save a
few bytes.

Finally unless the strings are long I might as well chuck the whole enum
concept and use a varchar(x) and stuff the whole string into the database and
not worry about any lookups or translations.

In my case I am working with relatively short strings (max ~15) so I think I
am going to just move ahead with the varchar(15) and remove any complexities
that would be introduced in my code with lookups.

Am I missing anything important?

Kind Regards,
Keith