Adding a new value to an existing ENUM Type

PostgreSQL 9.1 introduces ability to ALTER Enum types:

ALTER TYPE enum_type ADD VALUE 'new_value'; -- appends to listALTER TYPE enum_type ADD VALUE 'new_value' BEFORE 'old_value';ALTER TYPE enum_type ADD VALUE 'new_value' AFTER 'old_value';

NOTE if you're using PostgreSQL 9.1 or later, and you are ok with making changes outside of a transaction, see this answer for a simpler approach.

I had the same problem few days ago and found this post. So my answer can be helpful for someone who is looking for solution :)

If you have only one or two columns which use the enum type you want to change, you can try this. Also you can change the order of values in the new type.

-- 1. rename the enum type you want to changealter type some_enum_type rename to _some_enum_type;-- 2. create new typecreate type some_enum_type as enum ('old', 'values', 'and', 'new', 'ones');-- 3. rename column(s) which uses our enum typealter table some_table rename column some_column to _some_column;-- 4. add new column of new typealter table some_table add some_column some_enum_type not null default 'new';-- 5. copy values to the new columnupdate some_table set some_column = _some_column::text::some_enum_type;-- 6. remove old column and typealter table some_table drop column _some_column;drop type _some_enum_type;

3-6 should be repeated if there is more than 1 column.

A possible solution is the following; precondition is, that there are not conflicts in the used enum values. (e.g. when removing an enum value, be sure that this value is not used anymore.)

-- rename the old enumalter type my_enum rename to my_enum__;-- create the new enumcreate type my_enum as enum ('value1', 'value2', 'value3');-- alter all you enum columnsalter table my_table  alter column my_column type my_enum using my_column::text::my_enum;-- drop the old enumdrop type my_enum__;

Also in this way the column order will not be changed.