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

Re: How to modify ENUM datatypes?

From: "D(dot) Dante Lorenso" <dante(at)lorenso(dot)com>
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Cc: Andreas 'ads' Scherbaum <adsmail(at)wars-nicht(dot)de>, pgsql-general(at)postgresql(dot)org
Subject: Re: How to modify ENUM datatypes?
Date: 2008-04-22 21:34:55
Message-ID: 480E59FF.1020301@lorenso.com (view raw or flat)
Thread:
Lists: pgsql-general
Scott Marlowe wrote:
> On Tue, Apr 22, 2008 at 2:45 PM, D. Dante Lorenso <dante(at)lorenso(dot)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






> 


In response to

Responses

pgsql-general by date

Next:From: David WilsonDate: 2008-04-22 21:46:42
Subject: Re: Rapidly decaying performance repopulating a large table
Previous:From: John DeSoiDate: 2008-04-22 21:33:32
Subject: Re: Schema migration tools?

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