Re: enums

Gregory Maxwell wrote:

>And in doing so you could insert a enum in the middle of the existing
>list without breaking the values already in the table?  If so that
>would be very useful.
>
>  
>
You do it by altering the column type, not by altering the type itself.  
MySQL's way of doing this is made necessary by its horrid non-orthogonal 
way of doing enums. Here's how it works in PostgreSQL.  (To make this 
example work I had to add a text conversion - an inadvertant omission 
from the original. This is in a revised version of the enumkit, 
available at the same location.)


andrew=# create table foo (i serial, c rgb);
NOTICE:  CREATE TABLE will create implicit sequence "foo_i_seq" for 
serial column "foo.i"
CREATE TABLE
andrew=# insert into foo (c) values ('blue');
INSERT 8711471 1
andrew=# insert into foo (c) values ('green');
INSERT 8711472 1
andrew=# insert into foo (c) values ('red');
INSERT 8711473 1
andrew=# select * from foo order by c;i |   c  
---+-------3 | red2 | green1 | blue
(3 rows)

andrew=# insert into foo (c) values ('yellow');
ERROR:  invalid input value for enum: "yellow"
andrew=# alter table foo alter column c type rainbow using c::text;
ALTER TABLE
andrew=# select * from foo order by c;i |   c  
---+-------3 | red2 | green1 | blue
(3 rows)

andrew=# insert into foo (c) values ('yellow');
INSERT 8711477 1
andrew=# select * from foo order by c;i |   c   
---+--------3 | red4 | yellow2 | green1 | blue
(4 rows)


cheers

andrew