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!

Answer 1

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!