Thread: Poor-man's enumeration type
Hi All, I found this in the documentation. http://www.postgresql.org/docs/8.0/interactive/datatype-character.html 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? Can I expect this type to be supported in PostgreSQL 'forever'? I need an enum type and obviously this works. At the moment I am not faced with an imminent port but I hate to back myself into a corner if I do not have to. -- Kind Regards, Keith
Keith Worthington wrote: > Hi All, > > I found this in the documentation. > http://www.postgresql.org/docs/8.0/interactive/datatype-character.html > > 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? Can I expect this type > to be supported in PostgreSQL 'forever'? I need an enum Yes. > type and obviously this works. At the moment I am not > faced with an imminent port but I hate to back myself into > a corner if I do not have to. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
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.
> 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.
In general, enumeration can be handled by setting up a small table that
lists allowed values. e.g. (untested),
create table colors ( color text not null unique );
create table houses (
house_id serial not null unique
, house_color text not null
references colors (color) on update cascade on delete cascade
);
Hope this helps.
Michael Glaesemann
grzm myrealbox com
Michael Glaesemann <grzm@myrealbox.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
On Tue, 26 Apr 2005 01:06:51 -0400, Tom Lane wrote > Michael Glaesemann <grzm@myrealbox.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
"Keith Worthington" <keithw@narrowpathinc.com> writes:
> I honestly tried to check on the SQL standard but I was unable to find a copy
> online. Can you suggest a location?
The actual gold-plated spec is only available for $$$, but you can find
essentially-final drafts for free. See the PG FAQ for links. (I prefer
the drafts, personally, because you can get 'em in plain searchable
ASCII rather than barely-better-than-dead-trees PDF.)
> [snip]
> Am I missing anything important?
Didn't see anything ;-)
regards, tom lane
Tom Lane wrote: > "Keith Worthington" <keithw@narrowpathinc.com> writes: > > I honestly tried to check on the SQL standard but I was unable to find a copy > > online. Can you suggest a location? > > The actual gold-plated spec is only available for $$$, but you can find > essentially-final drafts for free. See the PG FAQ for links. (I prefer > the drafts, personally, because you can get 'em in plain searchable > ASCII rather than barely-better-than-dead-trees PDF.) Actually, the developer's FAQ. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073