Re: enums

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

In response to

  • Re: enums at 2005-10-28 06:02:43 from Gregory Maxwell

Browse pgsql-hackers by date

  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