I would still recommend to keep the meanings associated with the values in the database somehow.
Have you given thought to CHECK constraints? They are easier to alter on the fly: create table questionnare( Q varchar(256), A varchar(16) constraint possible_answers check ( A IN( 'yes', 'no') ) ); insert into questionnare values( 'dummy Q1', 'yes' ); insert into questionnare values( 'dummy Q2', 'no' ); begin; alter table questionnare drop constraint possible_answers ; alter table questionnare add constraint possible_answers check( A in ('yes', 'no', 'maybe' ) ); commit; \d questionnare insert into questionnare values( 'dummy Q3', 'maybe' ); select * from questionnare; Again, this operation will take long time depending on the number of rows in the table; HTH, best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad 18° 32' 57.25"N, 73° 56' 25.42"E - Pune 37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco * http://gurjeet.frihost.net Mail sent from my BlackLaptop device On Dec 26, 2007 10:17 AM, Henrique Pantarotto <[EMAIL PROTECTED]> wrote: > Thanks a lot Gurjeet! I understanded your suggestion... that seems to > work indeed. But I really would like to be able to alter the enum type > on the fly, so instead of using enum, I think I'll just use a "smallint" > type and tie the "possible results" to the application using flags such > as 0, 1, 2, 3 and whatever.. I think it will be easier for me this way. > > But thanks a lot anyway!! > > On Tue, 25 Dec 2007 23:08:12 -0800 > "Gurjeet Singh" <[EMAIL PROTECTED]> wrote: > > > Here's a possible solution (this may take long time if the table is too > > big). The trick is to add a new column with a newly defined datatype, > that > > picks up values from the old column. Here's the sample psql script (the > > session output follows after that): > > > > create type possible_answers as enum ( 'yes' , 'no' ); > > create table questionnare( Q varchar(256), A possible_answers ); > > insert into questionnare values( 'dummy Q1', 'yes' ); > > insert into questionnare values( 'dummy Q2', 'no' ); > > > > begin; > > create type possible_answers_new as enum ( 'yes' , 'no', 'maybe' ); > > > > alter table questionnare rename column A to B; > > alter table questionnare add column A possible_answers_new; > > > > update questionnare set A = B::text::possible_answers_new; > > > > alter table questionnare drop column B; > > commit; > > > > vacuum full questionnare; > > > > \d questionnare > > > > insert into questionnare values( 'dummy Q3', 'maybe' ); > > > > select * from questionnare; > > > > > > And here's what the session output looks like: > > > > postgres=# create type possible_answers as enum ( 'yes' , 'no' ); > > CREATE TYPE > > postgres=# create table questionnare( Q varchar(256), A possible_answers > ); > > insert into questionnare values( 'dummy Q1', 'yes' ); > > CREATE TABLE > > postgres=# insert into questionnare values( 'dummy Q1', 'yes' ); > > begin; > > INSERT 0 1 > > postgres=# insert into questionnare values( 'dummy Q2', 'no' ); > > INSERT 0 1 > > postgres=# > > postgres=# begin; > > BEGIN > > postgres=# create type possible_answers_new as enum ( 'yes' , 'no', > 'maybe' > > ); > > CREATE TYPE > > postgres=# > > postgres=# alter table questionnare rename column A to B; > > ALTER TABLE > > postgres=# alter table questionnare add column A possible_answers_new; > > ALTER TABLE > > postgres=# > > postgres=# update questionnare set A = B::text::possible_answers_new; > > UPDATE 2 > > postgres=# > > postgres=# alter table questionnare drop column B; > > commit; > > ALTER TABLE > > postgres=# commit; > > COMMIT > > postgres=# > > postgres=# vacuum full questionnare; > > > > VACUUM > > postgres=# > > postgres=# \d questionnare > > > > Table "public.questionnare" > > Column | Type | Modifiers > > --------+------------------------+----------- > > q | character varying(256) | > > a | possible_answers_new | > > > > postgres=# > > postgres=# insert into questionnare values( 'dummy Q3', 'maybe' ); > > INSERT 0 1 > > postgres=# > > postgres=# select * from questionnare; > > q | a > > ----------+------- > > dummy Q1 | yes > > dummy Q2 | no > > dummy Q3 | maybe > > (3 rows) > > > > postgres=# > > > > > > Hope it helps. > > -- > > [EMAIL PROTECTED] > > [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com > > > > EnterpriseDB http://www.enterprisedb.com > > > > 17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad > > 18° 32' 57.25"N, 73° 56' 25.42"E - Pune > > 37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco * > > > > http://gurjeet.frihost.net > > > > Mail sent from my BlackLaptop device > > On Dec 24, 2007 12:48 AM, Henrique Pantarotto <[EMAIL PROTECTED]> > wrote: > > > > > Hi, > > > > > > I was wondering how can I alter an ENUM type? I have created a table > > > like this: > > > > > > create type possible_answers as enum('yes', 'no'); > > > create table questions ( question text, answers possible_answers); > > > insert into questions values ('Do you like me?', 'yes'); > > > > > > So my question is... How can I change "possible_answers" to > enum('yes', > > > 'no', 'maybe')? > > > > > > I tried searching the documentation and mailing list, and I couldn't > > > figure this one out. > > > > > > > > > Thanks! > > > > > > > > > > > > > > > ---------------------------(end of > broadcast)--------------------------- > > > TIP 9: In versions below 8.0, the planner will ignore your desire to > > > choose an index scan if your joining column's datatypes do not > > > match > > > > > -- > Henrique Pantarotto <[EMAIL PROTECTED]> > http://henrique.pantarotto.com.br/ > >