Re: how to alter an enum type

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!

--- On Mon, 12/24/07, Henrique Pantarotto <henpa(at)terra(dot)com(dot)br> wrote:

> I tried searching the documentation and mailing list, and I
> couldn't
> figure this one out.

ALTER TABLE questions
ALTER COLUMN answers TYPE possible_answers;

Actually your type is fine. you only need to alter the column on your table to use your new type.

Regards,
Richard Broersma Jr.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--- On Mon, 12/24/07, Henrique Pantarotto <henpa(at)terra(dot)com(dot)br> wrote:

> I tried searching the documentation and mailing list, and I
> couldn't
> figure this one out.

ALTER TABLE questions
ALTER COLUMN answers TYPE possible_answers;

Actually your type is fine. you only need to alter the column on your table to use your new type.

Regards,
Richard Broersma Jr.

Hi Richard,

I actually want to change the enum values after I have created and
associated it to a table colum.

Is it possible?

Thanks.

On Mon, 24 Dec 2007 09:50:09 -0800 (PST)
Richard Broersma Jr <rabroersma(at)yahoo(dot)com> wrote:

> --- On Mon, 12/24/07, Henrique Pantarotto <henpa(at)terra(dot)com(dot)br> wrote:
>
> > I tried searching the documentation and mailing list, and I
> > couldn't
> > figure this one out.
>
> ALTER TABLE questions
> ALTER COLUMN answers TYPE possible_answers;
>
> Actually your type is fine. you only need to alter the column on your table to use your new type.
>
> Regards,
> Richard Broersma Jr.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

--
Henrique Pantarotto <henpa(at)terra(dot)com(dot)br>
http://henrique.pantarotto.com.br/

--- On Mon, 12/24/07, Henrique Pantarotto <henpa(at)terra(dot)com(dot)br> wrote:

> I actually want to change the enum values after I have
> created and
> associated it to a table colum.

It looks like you will have to drop the type and re-create it.

You might have to try a couple of tests:

1) BEGIN TRANSACTION;
DROP the type
create the type
commit; --does your table have still reference this type?

2) BEGIN TRANSACTION;
CREATE a new type with a different name
alter the table column to refer to the new type
drop the old type
Commit;

I wonder if it is possible to create an enum type using a select statement. This way the contents of the type can be seen in a base table. and it would be easy to recreate the type when new elements are added or removed. I'll have to play around with this in the future to see what is possible.

Regards,
Richard Broersma Jr.

On Mon, Dec 24, 2007 at 04:10:45PM -0200, Henrique Pantarotto wrote:
> Hi Richard,
>
> I actually want to change the enum values after I have created and
> associated it to a table colum.
>
> Is it possible?

No. An enum is defined by its members. You can't change a type after it
is created, not for enums, not for any type.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
> -- John F Kennedy

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
>

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/

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/
>
>