PostgreSQL 55P04 Error: Causes and Solutions Complete Guide

PostgreSQL Error 55P04: unsafe new enum value usage

PostgreSQL error 55P04 unsafe new enum value usage occurs when you try to use a newly added ENUM value within the same transaction that added it via ALTER TYPE ... ADD VALUE. Because PostgreSQL's system catalog changes for new ENUM values are not fully visible until the transaction commits, the engine refuses to use that value mid-transaction for safety. This is a fundamental behavior rooted in PostgreSQL's MVCC architecture.

---

Top 3 Causes

1. Adding and Using an ENUM Value in the Same Transaction

The most common scenario: you add a new ENUM label and immediately try to insert or update with it in the same BEGIN/COMMIT block.

-- ❌ This WILL fail with error 55P04
BEGIN;
ALTER TYPE order_status ADD VALUE 'pending_review';
INSERT INTO orders (status) VALUES ('pending_review');
COMMIT;

2. Single-File Migration Scripts Combining DDL and DML

Migration tools like Flyway, Liquibase, or Alembic often wrap all statements in a single transaction by default. Placing an ALTER TYPE ADD VALUE and subsequent data changes in the same migration file triggers this error.

-- ❌ Both statements run in one transaction — error on second line
ALTER TYPE user_role ADD VALUE 'super_admin';
UPDATE users SET role = 'super_admin' WHERE is_superuser = true;

3. Using New ENUM Values Inside PL/pgSQL Functions

When dynamic SQL inside a function or procedure adds an ENUM value and then references it within the same execution context (same transaction), the error surfaces.

-- ❌ This procedure will throw 55P04
CREATE OR REPLACE PROCEDURE bad_enum_usage()
LANGUAGE plpgsql AS $$
BEGIN
  EXECUTE 'ALTER TYPE product_category ADD VALUE ''refurbished''';
  INSERT INTO products (category) VALUES ('refurbished'); -- ❌ fails
END;
$$;

---

Quick Fix Solutions

Fix 1: Split into Separate Transactions

Always commit the ALTER TYPE ADD VALUE before using the new value.

-- ✅ Step 1: Add the ENUM value and commit
ALTER TYPE order_status ADD VALUE IF NOT EXISTS 'pending_review';
-- (autocommit or explicit COMMIT here)

-- ✅ Step 2: Use the new value in a NEW transaction
INSERT INTO orders (status) VALUES ('pending_review');

Fix 2: Separate Migration Files

Split your migration into two files/revisions so the ENUM change commits before the DML runs.

-- migration_001_add_enum_value.sql
ALTER TYPE order_status ADD VALUE IF NOT EXISTS 'pending_review';

-- migration_002_use_enum_value.sql  (runs after 001 commits)
UPDATE orders SET status = 'pending_review' WHERE needs_review = true;

For Alembic users, use separate revision files and ensure transaction_per_migration=True is set so each file gets its own commit.

Fix 3: Use IF NOT EXISTS for Idempotency

Always use IF NOT EXISTS to make your ENUM additions safe for re-runs.

-- ✅ Safe, idempotent ENUM addition
ALTER TYPE order_status ADD VALUE IF NOT EXISTS 'pending_review';
ALTER TYPE order_status ADD VALUE IF NOT EXISTS 'cancelled_by_admin';

---

Prevention Tips

1. Enforce a "One DDL Per Migration" Policy

Establish a team coding standard: any migration file containing ALTER TYPE ... ADD VALUE must contain only that statement — no DML in the same file. Enforce this with a SQL linter (e.g., sqlfluff) or a Git pre-commit hook that flags mixed DDL/DML files.

2. Consider Lookup Tables for Frequently Changing Enums

If your ENUM type changes often, replace it with a reference/lookup table. Regular INSERT statements have no transaction visibility restrictions and offer much more flexibility.

-- ✅ Lookup table approach — no 55P04 risk
CREATE TABLE order_statuses (
  code        VARCHAR(50) PRIMARY KEY,
  description TEXT,
  is_active   BOOLEAN DEFAULT true
);

-- Add new "enum values" freely within any transaction
INSERT INTO order_statuses (code, description)
VALUES ('pending_review', 'Awaiting manual review');

---

Related Errors

• 42710 duplicate_object — Thrown when you try to add an ENUM value that already exists. Use IF NOT EXISTS to avoid it.
• 0A000 feature_not_supported — Can appear in older PostgreSQL versions when attempting ALTER TYPE ADD VALUE inside an explicit transaction block.
• 25001 active_sql_transaction — A related error that signals DDL conflicts with an active transaction state during catalog operations.

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.