Re: How to modify ENUM datatypes? - Mailing list pgsql-general

Scott Marlowe wrote:
> On Tue, Apr 22, 2008 at 2:45 PM, D. Dante Lorenso <dante@lorenso.com> wrote:
>>  So, the advice here is "don't use ENUM"?
>>  I was really hoping that it would be more efficient to not have to do all
>> the foreign keys and joins for tables that may have 4-5 enum types.
>>  Just being able to:
>>   SELECT *
>>   FROM tablename
> If you use a "lookup table" methodology you still get that.  Try this:
> smarlowe=# create table choices (color text primary key);
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
> "choices_pkey" for table "choices"
> CREATE TABLE
> smarlowe=# insert into choices values ('red'),('yellow'),('green'),('blue');
> INSERT 0 4
> smarlowe=# create table mystuff (id serial primary key, usenam text,
> mycolor text references choices(color));
> NOTICE:  CREATE TABLE will create implicit sequence "mystuff_id_seq"
> for serial column "mystuff.id"
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
> "mystuff_pkey" for table "mystuff"
> CREATE TABLE
> smarlowe=# insert into mystuff (usenam, mycolor) values ('scott','red');
> INSERT 0 1
> smarlowe=# insert into mystuff (usenam, mycolor) values ('darren','blue');
> INSERT 0 1
> smarlowe=# insert into mystuff (usenam, mycolor) values ('dan','green');
> INSERT 0 1
> smarlowe=# insert into mystuff (usenam, mycolor) values ('steve','green');
> INSERT 0 1
> smarlowe=# insert into mystuff (usenam, mycolor) values ('mike','black');
> ERROR:  insert or update on table "mystuff" violates foreign key
> constraint "mystuff_mycolor_fkey"
> DETAIL:  Key (mycolor)=(black) is not present in table "choices".
> smarlowe=# select * from mystuff;
>  id | usenam | mycolor
> ----+--------+---------
>   1 | scott  | red
>   2 | darren | blue
>   3 | dan    | green
>   4 | steve  | green
> (4 rows)
> tada!  No enum, and no join.  But you can't insert illegal values in mycolor...

This approach is so old-school, I seem to have overlooked the obvious.

Here you've elected to use the foreign key to just control the possible
values inserted but not really to look up the value.

Seems you are storing the values in text form which goes against all the
normalization techniques I've learned in school.  I see this might be a
problem with storage since you will need to store the TEXT value for
every row in the 'mystuff' table instead of just storing the reference
to the lookup table as an INTEGER.  Over millions of rows, perhaps this
would become a concern?

What is the general consensus by the community about this approach?  Is
this de-normalization frowned upon, or is there a performance advantage
here that warrants the usage?

-- Dante






>