Adding a new value to an existing ENUM Type

Solution 1:

PostgreSQL 9.1 introduces ability to ALTER Enum types:

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

Solution 2:

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 change
alter type some_enum_type rename to _some_enum_type;
-- 2. create new type
create type some_enum_type as enum ('old', 'values', 'and', 'new', 'ones');
-- 3. rename column(s) which uses our enum type
alter table some_table rename column some_column to _some_column;
-- 4. add new column of new type
alter table some_table add some_column some_enum_type not null default 'new';
-- 5. copy values to the new column
update some_table set some_column = _some_column::text::some_enum_type;
-- 6. remove old column and type
alter table some_table drop column _some_column;
drop type _some_enum_type;

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

Solution 3:

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 enum
alter type my_enum rename to my_enum__;
-- create the new enum
create type my_enum as enum ('value1', 'value2', 'value3');

-- alter all you enum columns
alter table my_table
  alter column my_column type my_enum using my_column::text::my_enum;

-- drop the old enum
drop type my_enum__;

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