Adding a new value to an existing ENUM Type

PostgreSQL **9.1** introduces ability to [ALTER][1] 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';


[1]:

[To see links please register here]

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


[1]:

[To see links please register here]

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](

[To see links please register here]

)) 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 )

**Disclaimer:** I haven't tried this solution, so it might not work ;-)

You should be looking at `pg_enum`. If you only want to change the label of an existing ENUM, a simple UPDATE will do it.

To add a new ENUM values:

- First insert the new value into `pg_enum`. If the new value has to be the last, you're done.
- If not (you need to a new ENUM value in between existing ones), you'll have to update each distinct value in your table, going from the uppermost to the lowest...
- Then you'll just have to rename them in `pg_enum` in the opposite order.

**Illustration**<br />
You have the following set of labels:

ENUM ('enum1', 'enum2', 'enum3')

and you want to obtain:

ENUM ('enum1', 'enum1b', 'enum2', 'enum3')

then:

INSERT INTO pg_enum (OID, 'newenum3');
UPDATE TABLE SET enumvalue TO 'newenum3' WHERE enumvalue='enum3';
UPDATE TABLE SET enumvalue TO 'enum3' WHERE enumvalue='enum2';

then:

UPDATE TABLE pg_enum SET name='enum1b' WHERE name='enum2' AND enumtypid=OID;

And so on...

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.

As discussed above, `ALTER` command cannot be written inside a transaction. The suggested way is to insert into the pg_enum table directly, by `retrieving the typelem from pg_type table` and `calculating the next enumsortorder number`;

Following is the code that I use. (Checks if duplicate value exists before inserting (constraint between enumtypid and enumlabel name)

```
INSERT INTO pg_enum (enumtypid, enumlabel, enumsortorder)
SELECT typelem,
'NEW_ENUM_VALUE',
(SELECT MAX(enumsortorder) + 1
FROM pg_enum e
JOIN pg_type p
ON p.typelem = e.enumtypid
WHERE p.typname = '_mytypename'
)
FROM pg_type p
WHERE p.typname = '_mytypename'
AND NOT EXISTS (
SELECT * FROM
pg_enum e
JOIN pg_type p
ON p.typelem = e.enumtypid
WHERE e.enumlabel = 'NEW_ENUM_VALUE'
AND p.typname = '_mytypename'
)
```

Note that your type name is prepended with an underscore in the pg_type table. Also, the typname needs to be all lowercase in the where clause.

Now this can be written safely into your db migrate script.

**NOTE** if you're using PostgreSQL 9.1 or later, and you are ok with making changes outside of a transaction, see [this answer](

[To see links please register here]

) 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.

just in case, if you are using Rails and you have several statements you will need to execute one by one, like:

```
execute "ALTER TYPE XXX ADD VALUE IF NOT EXISTS 'YYY';"
execute "ALTER TYPE XXX ADD VALUE IF NOT EXISTS 'ZZZ';"
```

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

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


[1]:

[To see links please register here]

DB::statement("ALTER TABLE users DROP CONSTRAINT users_user_type_check");
$types = ['old_type1', 'old_type1', 'new_type3'];
$result = join( ', ', array_map(function ($value){
return sprintf("'%s'::character varying", $value);
}, $types));
DB::statement("ALTER TABLE users ADD CONSTRAINT users_user_type_check CHECK (user_type::text = ANY (ARRAY[$result]::text[]))");