Alter type Integer to Enum in Postgres

Question

I have a column called "abc_integer" which holds value of a foriegn key of a table which is only 2 values (1, 2). So I want to drop this column and add a new column "abc_enum" Note : I am trying this in Postgres 9.3 version

CREATE TYPE abc_enum_type ENUM AS ('hi', 'hello');
ALTER TABLE abc ADD COLUMN abc_enum abc_enum_type ;
UPDATE abc SET abc_enum = CASE 
           WHEN abc_integer == 1 THEN 'hi'::abc_enum_type
           ELSE 'hello'::abc_enum_type END ;
ALTER TABLE abc DROP COLUMN abc_intger;

Is there a better way doing this ? Like accommodating all of these in a single statement Alter type and rename with USING clause ?

Thanks in Advance!

Vao Tsun · Accepted Answer · 2016-11-17 12:08:56Z

8

You can "compress" it by one step with USING, like:

alter table abc alter COLUMN abc_integer set data type abc_enum_type using case when abc_integer == 1 THEN 'hi'::abc_enum_type ELSE 'hello'::abc_enum_type END;

and then renaming the column abc_integer to abc_enum.

If that what you want it works on 9.3

Btw! Mind default values.

Edit The way you do it does not lock the table. While altering column type will!

edited Nov 17, 2016 at 12:08

answered Nov 17, 2016 at 10:14

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Shankar Over a year ago

Thanks !! I don't have any default values .

Shankar Over a year ago

Is both the above mentioned same ? Both will take same time to execute? Because for one we are changing type , on other hand we are updating for new column .

Vao Tsun Over a year ago

yeah. forgot to mention :)

pg2286 Over a year ago

Is there a way to accommodate the default value too?

Collectives™ on Stack Overflow

Alter type Integer to Enum in Postgres

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related