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[.singh]@EnterpriseDB.com
singh.gurjeet@{ 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@xxxxxxxxxxxx> 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.gurjeet@xxxxxxxxx> 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[.singh]@EnterpriseDB.com
> singh.gurjeet@{ 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@xxxxxxxxxxxx> 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@xxxxxxxxxxxx>
