Alter Enums in PostgreSQL 9.x+, the real way

Recently, a project at work led me down a rabbit hole, to solve a seemingly common issue with writing migration scripts for our newly-transitioned PostgreSQL DB. The tool we’re using for migrations, Node DB Migrate (, handles everything in a big transaction block, and we have to (unfortunately) alter a Postgre 9.x-style first-class enum.

No problem, right? Wrong.

Turns out, PostgreSQL 9.1 introduced a fancy new one-liner for adding in a new value into an enum:

ALTER TYPE name ADD VALUE new_enum_value [ { BEFORE | AFTER } existing_enum_value ]

Not only did they not include a corresponding REMOVE command, they also disallowed this from being run within a multi-statement query or transaction block. Explicitly.

The rationale is (at least according to this StackOverflow answer) that if you’re changing values regularly, then enums are not for you…

…until they are, such as when a value is forgotten during initial design and needs to be added later.

However, there is a (hackish) solution that lets us get around this limitation, though it does leave one particular sticking point as an exercise for the reader, and best of all it works both ways.

Just replace the enum

That’s right, we mess around with how things are named, and just pretend it was that way from the get-go. More specifically, we have to create the enum we want with a new name, switch everything to use that name, drop the old enum, and then rename the enum so it matches the old enum’s name.

-- migrate-up.sql

-- Create the new enum completely; add/remove any values here
CREATE my_enum_new AS ENUM (

-- TODO: Handle existing records with removed enum values?

-- Alter affected tables to use the new version of the enum,
-- making sure to transition values through text to ensure
-- they transfer correctly
  ALTER COLUMN bar TYPE my_enum_new
    USING (bar::text::my_enum_new);

  ALTER COLUMN plugh TYPE my_enum_new
    USING (plugh::text::my_enum_new);

-- Drop the old type
DROP TYPE my_enum;

-- Rename to replace
ALTER TYPE my_enum_new RENAME TO my_enum;

Now, this works both ways; we can add and remove enum values using this notation, and as long as we aren’t removing an enum value which is in use, we won’t get a nasty error. Which brings us to our next quandary:

Handling existing records with removed enum values?

This is the part left up to the reader to determine. When you do a down migration, ostensibly, you’ll be getting rid of some data, because a once-valid enum value will simply cease to be there.

How you do it is up to you; in our system, we can’t delete records outright because we need to maintain data for legal purposes, but we can’t really shunt an enum to a different value, because that would be lying. However, since we caught this bug early on enough in our dev process, we can just tell the person running the migrations to fix it manually, by deliberately throwing an error in our migration scripts.

In the future, we would either update or delete rows to make them match, or do some other magick with marking records invalid or setting an entry into a legacy table. Clearly not a sustainable solution, but if we absolutely need to get modern records back into an older format (or, God forbid, remove an existing enum value going forward), that’s what we’ll have to do.

Until PostgreSQL fixes the transaction-block issue and puts in a robust single-line ALTER TYPE … REMOVE VALUE command, this is our general solution. This also falls apart if we want to do selective migrations; we’ll just have to deal with this as a risk.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s