PostgreSQL ALTER TYPE ADD VALUE Transaction Errors

The first ten minutes — establish facts before touching code.

• 1Run SHOW server_version first, then reproduce the migration in psql so you know whether the ALTER TYPE statement itself fails or only the first use of the new value fails.
• 2Inspect the migration boundary: if the script adds a value and then uses it in a default, constraint, or data update, split those operations across commits.
• 3Verify if the enum value already exists: SELECT enum_range(NULL::your_enum_type);
• 4Check whether the same transaction uses the new enum value after adding it; current PostgreSQL deliberately blocks that use until commit.
• 5Review migration order: if you add a value and then use it in a column default or constraint in the same migration, it will fail because the new value isn't committed.

The specific files, logs, configs, and dashboards that usually own this bug.

• searchMigration script files (e.g., alembic/versions/*.py, flyway/V*.sql)
• searchpsql logs or migration tool logs (e.g., alembic.log, flyway-*.log)
• searchPostgreSQL error log: typically /var/log/postgresql/postgresql-*.log or via pg_stat_activity
• searchDatabase schema dump: pg_dump --schema-only your_db
• searchEnum type definition: SELECT oid, typname FROM pg_type WHERE typcategory='E'; then SELECT enumlabel FROM pg_enum WHERE enumtypid=oid;
• searchCurrent transaction state: SELECT pg_current_xact_id_if_assigned(); (if inside transaction, returns an XID)

Practical causes, not theory. These are the things you will actually find.

• warningAn older PostgreSQL release rejects ADD VALUE inside the transaction opened by the migration tool
• warningThe migration adds an enum value and then tries to use it before the surrounding transaction commits
• warningAttempting to add an enum value that already exists (duplicate value error)
• warningUsing BEFORE or AFTER with a neighboring enum label that does not exist
• warningUsing the new enum value in the same transaction before it is committed
• warningRunning multiple migrations concurrently that both add values to the same enum type, causing deadlocks

Concrete fix directions. Pick the one that matches your root cause.

• buildOn current PostgreSQL, commit the ADD VALUE migration before a later migration uses the new enum value.
• buildOn older PostgreSQL, configure the migration tool to execute the ALTER TYPE statement outside its transaction wrapper.
• buildFor raw SQL on an older server, remove the surrounding BEGIN/COMMIT or use a separate autocommit connection.
• buildUse IF NOT EXISTS where the deployed PostgreSQL version supports it, or guard the change by checking pg_enum before rerunning it.
• buildAdd the enum value in a separate migration step that runs before any code that uses it.
• buildUse ALTER TYPE ... ADD VALUE ... BEFORE/AFTER to maintain ordering if needed.

A fix you cannot prove is a guess. Close the loop.

• verifiedAfter running the migration, connect to the database and run SELECT enum_range(NULL::your_enum_type); to see the new value.
• verifiedVerify that new rows can be inserted with the new enum value.
• verifiedCheck that no errors appear in application logs when using the new value.
• verifiedRun the migration a second time (idempotency check)—it should skip or gracefully handle if the value already exists.
• verifiedTest rollback before deployment; PostgreSQL has no direct DROP VALUE command, so plan data reconciliation and type recreation if a committed value must be removed.

Things that make this bug worse or harder to find.

• warningDo not add the enum value and then use it in the same transaction; current PostgreSQL requires the new value to be committed first.
• warningDo not apply advice for one PostgreSQL version without checking the production server version.
• warningDo not add duplicate values—Postgres will throw an error; always check existing values first.
• warningDo not ignore the ordering: if your application expects a specific order, use BEFORE/AFTER appropriately.
• warningDo not run multiple migrations that add values to the same enum type concurrently—they can deadlock.

Frequently asked questions