Re: What is the good equivalent for ENUM ?

On Wed, 3 Sep 2003, Ron Johnson wrote:

> It'll be better when domains have alterable constraints.  Your
> way is the traditional (and best, IMO) way, though.

This is similar to what I have tended to use, but I have always wondered
about the efficency, and have used an int4 serial column as the primary
key and used this to reference the status data.

This has made it a little frustrating sometimes to write queries though.
Is referencing the varchar column generally reasonable in postgresql, and
not likely to give big performance issues as the tables get larger?
(sorry, this is a little hand wavy and not very exact, but I am wondering
if i am getting carried away giving everything an id)

This is the type of thing I have used: (edited from a couple of posts ago)

> > CREATE TABLE status_levels (
> >  status_levels_id serial primary key,
     status varchar(10)
> > ) WITHOUT OIDS;
> > INSERT INTO status_levels (status) VALUES ('active');
> > INSERT INTO status_levels (status) VALUES ('overdue');
> > INSERT INTO status_levels (status) VALUES ('suspended');
> > INSERT INTO status_levels (status) VALUES ('terminated');
> >
> > then reference it via foreign key from the "enum" field:
> >
> > CREATE TABLE whatever (
> >  ...
> >  status int4 NOT NULL DEFAULT 1 REFERENCES
status_levels(status_levels_id),
> >  ...
> > );
> >