From: | "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | "D(dot) Dante Lorenso" <dante(at)lorenso(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:14:14 |
Message-ID: | dcc563d10804221414w6111b09bpdf6c98dc85519a31@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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...
From | Date | Subject | |
---|---|---|---|
Next Message | David Wilson | 2008-04-22 21:15:11 | Re: Rapidly decaying performance repopulating a large table |
Previous Message | Gurjeet Singh | 2008-04-22 21:10:01 | Re: How to modify ENUM datatypes? |