PostgreSQL

PostgreSQL Enum Type Migration Failing in Production

PostgreSQL enum migrations failing specifically in production, despite working fine in local testing, is almost always related to ALTER TYPE ... ADD VALUE's specific restriction against running inside a transaction block together with other statements that use the new value — a limitation many migration frameworks don't handle correctly by default.

The Problem

A migration that adds a new value to an existing enum type fails with:

ERROR:  ALTER TYPE ... ADD cannot run inside a transaction block
ERROR:  unsafe use of new value "shipped" of enum type order_status
DETAIL:  New enum values must be committed before they can be used.

The exact same migration might have appeared to work in a local test, or worked the first time but failed on a second, related change, adding to the confusion.

Why It Happens

PostgreSQL has a specific, deliberate restriction around adding new enum values: the new value cannot be used in the same transaction that adds it, because internally the new value doesn't have a guaranteed sort position relative to other values until the addition is fully committed. Common causes of this surfacing specifically in production:

  • A migration tool wraps every migration in a single transaction by default (common, sensible behavior for most migrations, since it makes them atomic and safely reversible on failure), but this exact behavior breaks specifically for ALTER TYPE ... ADD VALUE combined with any later statement using that new value
  • A single migration file both adds the enum value and immediately uses it (for example, updating existing rows to use the new status), which fails specifically because both actions happen inside the same transaction
  • Local testing happened to only test the "add the value" part in isolation, without a subsequent step that actually uses it in the same deploy, masking the issue until a more complete migration hit production
  • PostgreSQL version differences — older versions had even stricter restrictions around enum modifications in general, so behavior that works on a newer local Postgres version might not replicate exactly against an older production version

The Fix

Split the enum value addition into its own separate migration, committed on its own, before any migration that actually uses the new value:

-- Migration 1: add the value only
ALTER TYPE order_status ADD VALUE 'shipped';
-- Migration 2 (separate deploy or separate transaction): use the new value
UPDATE orders SET status = 'shipped' WHERE ...;

These need to run as genuinely separate transactions, not just separate statements within the same transactional migration file, since the restriction is specifically about transaction boundaries, not statement count.

If your migration tool supports running a specific migration outside its default transaction wrapping, use that option specifically for the enum addition step:

# Example: many migration tools support a per-migration flag
# to disable the automatic transaction wrapper for this specific file

Check your specific migration framework's documentation for the exact mechanism — the concept (opting a specific migration out of automatic transaction wrapping) is common across most modern migration tools, even though the exact syntax varies.

If your workflow requires both actions to happen in a single deploy without a separate migration step in between, use ALTER TYPE ... ADD VALUE IF NOT EXISTS combined with explicit transaction control, running the addition in an autocommit context before continuing:

ALTER TYPE order_status ADD VALUE IF NOT EXISTS 'shipped';
COMMIT;
-- new transaction starts here, can now safely use 'shipped'
UPDATE orders SET status = 'shipped' WHERE ...;

As an alternative approach entirely, consider whether a plain VARCHAR column with an application-level or check-constraint-based validation might serve your needs better than a native Postgres enum, specifically because enums in Postgres have historically had more friction around this kind of modification compared to simpler text-based alternatives — this tradeoff is worth considering if your enum values change relatively often.

Still Not Working?

If splitting into separate transactions still fails, confirm you're not accidentally running both migrations within a single outer transaction imposed by your deployment pipeline itself (some CI/CD deployment scripts wrap an entire deploy's database changes in one transaction for atomicity, which reintroduces the same problem at a higher level than your migration tool's own per-migration transaction handling). In that case, the enum addition needs to be deployed and committed as a genuinely separate deployment step, not just a separate migration file within the same deploy transaction.

You might also like