Re: How to modify ENUM datatypes?

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...