ALTER ENUM in PostgreSQL

Note: do not use this method on PostgreSQL 9.1. This can corrupt your catalog as new enum model differentiates between even and odd OIDs for enum values. But in PostgreSQL 9.1 there is a nice ALTER TYPE command, that can alter you enum correctly.

Unfortunately PostgreSQL (9.0 and before) does not support altering ENUM types, that makes their use quite limited. Very often, it is important to have a possibility to extend already existing enumeration type and in very few cases delete or rename an element of an enumeration type.

The standard way to do that, would be to create a new enumeration type with some temporary name, convert the fields of the type of your old enumeration type to the new enumeration type you just created, then drop the old one and rename the temporary name to the original enum type. This operation is doable, but conversion of the field type is usually getting a full lock on that table and can be quite a long operation (maybe in 9.1 this will change).

By now, the only way to do it without table lock and messing up with temporary enumeration types, is to play with the catalog directly.

As internally postgres is storing OIDs of the enumeration type labels in the tables and types, where this enumeration is being used, one can actually add new labels and change the names of existing labels in the needed enumeration without a fear to destroy integrity of existing data structures, that use that enumeration labels already (i.e. OIDs of that enumeration labels).

To add one additional label in an existing ENUM one can do the following:

insert into pg_enum(enumtypid, enumlabel)
select t.oid, 'NEW_ENUM_VALUE'
  from pg_type as t
 where t.typtype = 'e'
   and t.oid = 'existing_enum'::regtype::oid
   and string_to_array( split_part(version(), ' ', 2), '.' )::int[] < ARRAY[9,1]
returning enumtypid::regtype::text, enumlabel;

This will add a label of NEW_ENUM_VALUE to an existing enumeration type existing_enum (be sure to put the right letter case for your enumeration type and enumeration label names).

To rename an existing value name one can do the following:

update pg_enum as e
   set enumlabel = 'UPDATED_ENUM_VALUE'
 where e.enumtypid = 'existing_enum'::regtype::oid
   and e.enumlabel = 'OLD_ENUM_VALUE'
   and string_to_array( split_part(version(), ' ', 2), '.' )::int[] < ARRAY[9,1]
returning e.enumtypid::regtype::text, e.enumlabel;

Deletion of the enumeration labels can be really dangerous as one can accidentally drop an OID of the label, that is still being used in the tables. Dmitry Koterov wrote a stored procedure, that does some easiest checks on tables (not types or types of types) that use the enumeration that you want to change. So if you really need to drop an enumeration label from an existing enumeration, think twice first and then read the post with the source of this stored procedure :)