Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

Next:From: Merlin MoncureDate: 2005-10-28 12:41:36
Subject: Re: enums
Previous:From: Alvaro HerreraDate: 2005-10-28 11:31:26
Subject: Re: TODO Item - Add system view to show free space map

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group