From: | Andrew Dunstan <andrew(at)dunslane(dot)net> |
---|---|
To: | Gregory Maxwell <gmaxwell(at)gmail(dot)com> |
Cc: | Jim Nasby <jnasby(at)pervasive(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: enums |
Date: | 2005-10-28 12:39:59 |
Message-ID: | 43621C1F.4050409@dunslane.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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 | red
2 | green
1 | 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 | red
2 | green
1 | blue
(3 rows)
andrew=# insert into foo (c) values ('yellow');
INSERT 8711477 1
andrew=# select * from foo order by c;
i | c
---+--------
3 | red
4 | yellow
2 | green
1 | blue
(4 rows)
cheers
andrew
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2005-10-28 12:41:36 | Re: enums |
Previous Message | Alvaro Herrera | 2005-10-28 11:31:26 | Re: TODO Item - Add system view to show free space map |