Thread: Poor-man's enumeration type

Poor-man's enumeration type

From
Keith Worthington
Date:
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

Re: Poor-man's enumeration type

From
Bruce Momjian
Date:
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

Re: Poor-man's enumeration type

From
Michael Glaesemann
Date:
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


Re: Poor-man's enumeration type

From
Tom Lane
Date:
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

Re: Poor-man's enumeration type

From
"Keith Worthington"
Date:
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

Re: Poor-man's enumeration type

From
Tom Lane
Date:
"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

Re: Poor-man's enumeration type

From
Bruce Momjian
Date:
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