Skip site navigation (1) Skip section navigation (2)

Re: enums

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Rod Taylor <pg(at)rbt(dot)ca>
Cc: Andrew Dunstan <andrew(at)dunslane(dot)net>,PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>,Michael Fuhr <mike(at)fuhr(dot)org>
Subject: Re: enums
Date: 2005-10-28 18:20:51
Message-ID: 20051028182051.GJ13187@pervasive.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

In response to

  • Re: enums at 2005-10-28 03:07:19 from Rod Taylor

Responses

  • Re: enums at 2005-10-28 18:57:03 from Rod Taylor

pgsql-hackers by date

Next:From: Jim C. NasbyDate: 2005-10-28 18:23:27
Subject: Re: enums
Previous:From: Jim C. NasbyDate: 2005-10-28 18:04:03
Subject: Re: TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)",

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group