PostgreSQL supports custom enum types, which is awesome. However, extending them is kind of a pain, since there is no command to just add a new value to the enum.

Thanks to this StackOverflow answer, there are two alternate ways to still get what you want. One works by modifying the pg_enum, which strikes me as a bit of a bad idea, since those tables are under Postgres’ direct control.

The other one works by adding a new type, and adding a new column, then copying over all the old values, and finally dropping the old columns and types (via taksofan):

ALTER TYPE enum_type RENAME TO _old_enum_type;

-- "Extend" enum_type with a new value: d
CREATE TYPE enum_type AS ENUM ( 'a', 'b', 'c', 'd' );

ALTER TABLE a_table RENAME COLUMN the_enum_column TO _old_enum_column;
ALTER TABLE a_table ADD COLUMN the_enum_column enum_type;

UPDATE a_table SET the_enum_column = _old_enum_column::text::enum_type;

ALTER TABLE a_table DROP COLUMN _old_enum_column;
DROP TYPE _old_enum_type;

This works well enough for my purposes, but will probably not do so well with very large tables.

PostgreSQL 9.1 will be able to extend enum types with much less fuss:

ALTER TYPE enum_type ADD 'new_value' BEFORE 'old_value';

Update 2011-11-08

If you want to change the enum type used in a large table, then the method described above will very likely take a long time to finish.

Michael Renner and RhodiumToad on Freenode/#postgresql pointed out two different ways speed up this process.

The faster, safe way

The first one works like this:

  • You create your new, extended enum type
  • You alter your column to use that new enum type. This requires a single bulk rewrite of the table, but this is much faster than copying over the old values into a new value.
  • Drop the old value

Or, in SQL:

ALTER TYPE enum_type RENAME TO _old_enum_type;

CREATE TYPE enum_type AS ENUM ( 'a', 'b', 'c', 'd' );

ALTER TABLE a_table ALTER COLUMN the_enum_column TYPE enum_type;

DROP TYPE _old_enum_type;

The even faster, but possibly very dangerous, way

The second one is decidedly more dangerous, so make sure you test everything thouroghly.

First, for this to work, you must ensure that no OID wraparound has happened. How can you tell if an OID wraparound has happened? Execute this query:

CREATE TEMP TABLE foo ();
SELECT 'foo'::regclass::oid;

Then make sure if the returned OID is larger than both the smallest and largest value of the enum.

And secondly, this messes around in PostgreSQL internals, so there might be other side effects. Use this at your own risk.

Here goes:

-- PostgreSQL 9

INSERT INTO pg_enum (enumtypid, enumlabel) VALUES
    ('enum_type'::regtype, 'd');

-- PostgreSQL 10
-- Here, we set enumsortorder to be one higher than the highest sort order for the type
INSERT INTO pg_enum (enumtypid, enumlabel, enumsortorder) VALUES
    ('enum_type'::regtype, 'd', (SELECT MAX(enumsortorder) FROM pg_enum WHERE enumtypid = 'enum_type'::regtype) + 1);