Idempotent Enum and Type Changes in PostgreSQL

The migration that adds 'refunded' to the order_status enum ran in staging, failed in production on a lock timeout further down the file, and the retry now dies on its first line: ERROR: type "order_status" already exists, or ERROR: enum label "refunded" already exists. Tables and columns have IF NOT EXISTS; types are the awkward corner of PostgreSQL DDL where that guard is partial. CREATE TYPE has no IF NOT EXISTS at all, ALTER TYPE ... ADD VALUE gained IF NOT EXISTS but has transaction restrictions that trip up migration tools, and enum values can never be removed. This guide covers the guard patterns for each, as part of the Idempotent Script Design toolkit.

Guard Support for Type DDL Matrix of PostgreSQL type operations against native idempotent guard support and restrictions. CREATE TYPE AS ENUM has no IF NOT EXISTS; use a DO block catching duplicate_object. ALTER TYPE ADD VALUE supports IF NOT EXISTS; before PostgreSQL 12 it cannot run in a transaction block, and from 12 the new value cannot be used in the same transaction. RENAME VALUE has no guard and needs a catalog check. Removing a value is not supported. Guard Support for Type DDL Operation Native guard Restriction Idempotent pattern CREATE TYPE … AS ENUM none none DO block, catch duplicate_object ALTER TYPE … ADD VALUE IF NOT EXISTS PG < 12: not in a txn block ADD VALUE IF NOT EXISTS new value used in same txn n/a PG 12+: unsafe until commit separate migration ALTER TYPE … RENAME VALUE none PG 10+ check pg_enum first remove an enum value not supported — new type + column swap
Enums are append-only and only partly guardable; every row needs its own pattern.

Symptom / Error Signatures

Non-idempotent type migrations fail on rerun with:

ERROR:  type "order_status" already exists                    -- SQLSTATE 42710 duplicate_object
ERROR:  enum label "refunded" already exists                  -- SQLSTATE 42710
ERROR:  ALTER TYPE ... ADD VALUE cannot run inside a transaction block   -- PostgreSQL 11 and earlier
ERROR:  unsafe use of new value "refunded" of enum type order_status    -- PostgreSQL 12+, SQLSTATE 55P04
HINT:  New enum values must be committed before they can be used.

The last one is the most confusing: the migration adds a value and then, in the same transaction, uses it — in a DEFAULT, a CHECK constraint, an UPDATE or an index predicate — and PostgreSQL refuses.

Root Cause Analysis

CREATE TYPE predates the widespread use of IF NOT EXISTS in PostgreSQL DDL and never received the clause, so a rerun always fails with duplicate_object. The standard workaround is a DO block that attempts the creation and catches that specific exception — atomic, because the whole block runs as one statement, and harmless when the type exists.

ALTER TYPE ... ADD VALUE has supported IF NOT EXISTS since PostgreSQL 9.3, so the value itself is easy to guard. The restrictions come from how enum values are stored: each value is a row in pg_enum with a sort order, and indexes on enum columns depend on those rows being stable. Before PostgreSQL 12, adding a value was forbidden inside a transaction block altogether, which collides with every tool that wraps migrations in transactions. Since PostgreSQL 12 it is allowed, but the new value cannot be used until the adding transaction commits, because an uncommitted pg_enum row could be rolled back after an index already references it.

Removing a value is not supported at all. The only way to drop one is to create a new type without it, migrate columns to the new type, and drop the old type — a type change that rewrites the table and needs the full column type change treatment.

Why the New Value Must Be Committed First Sequence between the migration and PostgreSQL. In one transaction the migration adds value refunded, then tries UPDATE orders SET status = refunded; PostgreSQL rejects the update with unsafe use of new value. In the fixed version the first migration adds the value and commits, and a second migration uses it. Why the New Value Must Be Committed First Migration 1 Migration 2 PostgreSQL BEGIN; ADD VALUE IF NOT EXISTS 'refunded' UPDATE … SET status = 'refunded' ERROR 55P04 unsafe use of new value fix: commit after ADD VALUE ADD VALUE IF NOT EXISTS; COMMIT UPDATE … SET status = 'refunded' UPDATE 1842
Split "add the value" and "use the value" into separate migrations; the commit between them is what makes the new label safe to reference.

Immediate Mitigation

1. Guard CREATE TYPE with a DO block. Catch only duplicate_object, so any other failure still surfaces.

-- PostgreSQL 9.6+ · migration role with CREATE on the schema · safe inside a transaction
DO $$
BEGIN
  CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped');
EXCEPTION
  WHEN duplicate_object THEN NULL;   -- already exists: nothing to do
END $$;
-- ROLLBACK PATH: DROP TYPE IF EXISTS order_status;  (only if no column uses it)

The guard matches by name. If the type exists with different values, the block silently skips, so follow it with the value-adding statements below, which bring any existing type up to date.

2. Add enum values with IF NOT EXISTS, in their own migration. On PostgreSQL 12+ this can run inside the tool’s transaction; on 11 and earlier, mark the migration non-transactional as described in running concurrent index builds outside migration transactions.

-- PostgreSQL 12+ · migration role · ACCESS EXCLUSIVE is not needed; brief lock on the type only
-- WARNING: do not reference 'refunded' anywhere else in this migration.
ALTER TYPE order_status ADD VALUE IF NOT EXISTS 'refunded' AFTER 'shipped';
-- ROLLBACK PATH: none — enum values cannot be dropped; leave it unused or rebuild the type.

3. Use the value in a later migration. Defaults, constraints, backfills and partial indexes that reference 'refunded' go in the next migration, after the adding one has committed.

4. Guard renames with a catalog check. RENAME VALUE fails if the old label is absent, which it will be on a rerun.

-- PostgreSQL 10+ · migration role · rename only if the old label is still present
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_enum e JOIN pg_type t ON t.oid = e.enumtypid
             WHERE t.typname = 'order_status' AND e.enumlabel = 'shipped') THEN
    ALTER TYPE order_status RENAME VALUE 'shipped' TO 'dispatched';
  END IF;
END $$;
-- ROLLBACK PATH: the same block with the labels swapped.

Remember that a rename changes the stored meaning immediately for every reader, so deployed code that still writes 'shipped' will fail — treat a rename like a column rename and coordinate it with an expand-and-contract release.

Permanent Fix / Long-Term Pattern

Adopt three rules for enum types in migrations. Create types only through the guarded DO block. Add values only with ADD VALUE IF NOT EXISTS, alone in their migration, and never use a new value in the migration that adds it. And never plan on removing values: if a domain is expected to shrink or change often, use a lookup table with a foreign key or a text column with a CHECK constraint instead, both of which can be altered online with the patterns in Adding Constraints Without Downtime.

Order the release so that code tolerates the new value before anything writes it. Deploy the migration that adds the value, then deploy application code that can read it, and only then enable code paths that write it — the same sequencing as any additive change in Expand and Contract Methodology. ORMs that map enums to native types (Prisma, SQLAlchemy, TypeORM) generate ADD VALUE statements; review the generated migration for any use of the new value in the same file.

Enum Type or Something Else? Decision tree for modelling a set of allowed values. If values will only ever be appended, a native enum is fine. If values may be removed or renamed often, check whether they need extra attributes; if so use a lookup table with a foreign key, if not use a text column with a CHECK constraint. Enum Type or Something Else? Will values only ever be appended? yes no Native enum + ADD VALUE IF NOT EXISTS Do values need extra attributes? yes no Lookup table + foreign key text column + CHECK constraint
Native enums are cheap and compact but append-only; if the set will shrink or churn, choose a structure you can change online.

Verification Checklist

Frequently Asked Questions

Why doesn’t CREATE TYPE support IF NOT EXISTS? It simply never gained the clause, unlike tables, indexes and schemas. The DO block that catches duplicate_object is the accepted workaround and is safe to run inside a transaction.

Can I add an enum value inside a transaction? On PostgreSQL 12 and later, yes, but you cannot use the new value until that transaction commits. On PostgreSQL 11 and earlier, the statement is rejected inside a transaction block, so the migration must run non-transactionally.

Does ADD VALUE lock the table? No table rewrite or table lock is involved; it inserts a row into pg_enum and takes a lock on the type. Adding a value in the middle of the sort order with BEFORE or AFTER is also cheap in modern versions.

How do I remove an enum value? There is no DROP VALUE. Create a new enum type without the value, convert columns to it (which rewrites them), and drop the old type — or switch to a text column with a CHECK constraint, which can be changed without rewriting the table.

Related Articles