Re: how to alter an enum type

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,
--
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ 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 <henpa(at)terra(dot)com(dot)br> 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" <singh(dot)gurjeet(at)gmail(dot)com> 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.
> > --
> > gurjeet[(dot)singh](at)EnterpriseDB(dot)com
> > singh(dot)gurjeet(at){ 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 <henpa(at)terra(dot)com(dot)br>
> 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 <henpa(at)terra(dot)com(dot)br>
> http://henrique.pantarotto.com.br/
>
>