Shankar Shankar - 1 year ago 125
SQL Question

Alter type Integer to Enum in Postgres

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 ;

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!

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download