Re: enums

On Fri, 2005-10-28 at 13:20 -0500, Jim C. Nasby wrote:
> On Thu, Oct 27, 2005 at 11:07:19PM -0400, Rod Taylor wrote:
> > > The other issue is ease of use.
> > >
> > > We used lookup tables in bugzilla when it was converted to work with
> > > Postgres. But many users will find having to do that annoying, to say
> > > the least. I think there's a very good case for providing true enums.
> >
> > Then why did you use lookup tables instead of a varchar and a
> > constraint? Probably performance.
> >
> > A much more general purpose but just as good solution would be the
> > ability to create a hidden surrogate key for a structure.
> >
> > CREATE TABLE status (code varchar(20) PRIMARY KEY) WITH SURROGATE;
> > CREATE TABLE account (name varchar(60), status varchar(20) references
> > status);
> >
> > Behind the scenes (transparent to the user) this gets converted to:
> >
> > CREATE TABLE status (id SERIAL UNIQUE, code varchar(20) PRIMARY KEY)
> > WITH SURROGATE;
> > CREATE TABLE account (name varchar(60), status integer references
> > status(id));
> >
> >
> > SELECT * FROM account; would be rewritten as
> > SELECT * FROM (SELECT name, code FROM account JOIN status USING (id)) AS
> > account;
> >
> > Enum might be good for a short list of items but something like the
> > above should be good for any common value that we manually create
> > surrogate keys for today but without the clutter or the application
> > needing to know.
> >
> > If PostgreSQL had an updatable view implementation it would be pretty
> > simple to implement.
>
> I'm not quiet following the WITH SURROGATE bit, but what you've
> described certainly looks valuable. Note that I would still want to be
> able to get at the raw numeric values in some fasion.

The basic idea is that most of us break out schemas by creating fake
primary keys for the purpose of obtaining performance because using the
proper primary key (single or multiple columns) is often very slow.

The automatic and transparent creation of a surrogate key by PostgreSQL
would allow us to dramatically clean up the presentation of our schema
to the users using the database without the performance hit we currently
get.

It puts surrogate keys (fake primary keys) back to the level of table
spaces, indexes and other performance enhancements where they belong.

--