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