Adding a new value to an existing ENUM Type

Question

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';

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.

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.

If you fall into situation when you should add enum values in transaction, f.e. execute it in flyway migration on ALTER TYPE statement you will be get error ERROR: ALTER TYPE ... ADD cannot run inside a transaction block (see flyway issue #350) you could add such values into pg_enum directly as workaround (type_egais_units is name of target enum):

INSERT INTO pg_enum (enumtypid, enumlabel, enumsortorder)
    SELECT 'type_egais_units'::regtype::oid, 'NEW_ENUM_VALUE', ( SELECT MAX(enumsortorder) + 1 FROM pg_enum WHERE enumtypid = 'type_egais_units'::regtype )

If you are using Postgres 12 (or later) you can just run ALTER TYPE ... ADD VALUE inside of transaction (documentation).

If ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum type) is executed inside a transaction block, the new value cannot be used until after the transaction has been committed.

So no hacks needed in migrations.

UPD: here is an example (thanks to Nick for it)

ALTER TYPE enum_type ADD VALUE 'new_value';

Complementing @Dariusz 1

For Rails 4.2.1, there's this doc section:

== Transactional Migrations

If the database adapter supports DDL transactions, all migrations will automatically be wrapped in a transaction. There are queries that you can't execute inside a transaction though, and for these situations you can turn the automatic transactions off.

class ChangeEnum < ActiveRecord::Migration
  disable_ddl_transaction!

  def up
    execute "ALTER TYPE model_size ADD VALUE 'new_value'"
  end
end