Re: how to alter an enum type
| От | Gurjeet Singh |
|---|---|
| Тема | Re: how to alter an enum type |
| Дата | |
| Msg-id | 65937bea0712252308m5e5ab2bdl47fcf71f484d00f@mail.gmail.com обсуждение исходный текст |
| Ответ на | how to alter an enum type (Henrique Pantarotto <henpa@terra.com.br>) |
| Ответы |
Re: how to alter an enum type
(Henrique Pantarotto <henpa@terra.com.br>)
|
| Список | pgsql-general |
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
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@terra.com.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
В списке pgsql-general по дате отправления:
Предыдущее
От: Alvaro HerreraДата:
Сообщение: Re: Retrying transactions in serializable isolation level