DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 55P04 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

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. PostgreSQL intentionally blocks this to protect system catalog consistency — the new value isn't considered fully committed until the transaction ends. This error is especially common in automated migration scripts that wrap all statements in a single transaction.

Top 3 Causes

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

This is by far the most common cause. PostgreSQL will raise 55P04 the moment you reference a freshly added ENUM value before the transaction commits.

-- ❌ This will fail with 55P04
BEGIN;
ALTER TYPE order_status ADD VALUE 'PENDING_REVIEW';
INSERT INTO orders (customer_id, status)
VALUES (42, 'PENDING_REVIEW'); -- ERROR: 55P04 here!
COMMIT;
Enter fullscreen mode Exit fullscreen mode 
-- ✅ Correct: commit the ALTER TYPE first, then use the new value
ALTER TYPE order_status ADD VALUE IF NOT EXISTS 'PENDING_REVIEW';
-- (runs as its own autocommit transaction)

BEGIN;
INSERT INTO orders (customer_id, status)
VALUES (42, 'PENDING_REVIEW');
COMMIT;
Enter fullscreen mode Exit fullscreen mode

2. Migration Tools Wrapping DDL + DML in One Transaction

Tools like Flyway, Liquibase, and Alembic wrap all statements in a migration file into a single transaction by default. If your migration file adds an ENUM value and then immediately uses it, the deployment will fail.

-- ❌ Single migration file — will fail in Flyway/Liquibase
ALTER TYPE mood ADD VALUE 'ecstatic';
UPDATE user_profiles SET current_mood = 'ecstatic' WHERE active = TRUE;
Enter fullscreen mode Exit fullscreen mode 
-- ✅ Split into two separate migration files

-- V1__add_mood_enum.sql (set executeInTransaction=false in Flyway)
ALTER TYPE mood ADD VALUE IF NOT EXISTS 'ecstatic';

-- V2__update_mood_data.sql (normal transactional migration)
BEGIN;
UPDATE user_profiles SET current_mood = 'ecstatic' WHERE active = TRUE;
COMMIT;
Enter fullscreen mode Exit fullscreen mode

3. Using New ENUM Value Inside a DO Block or Procedure

Anonymous PL/pgSQL DO blocks run inside an implicit transaction context. Adding an ENUM value and referencing it in the same block triggers 55P04.

-- ❌ Will fail inside a DO block
DO $$
BEGIN
    ALTER TYPE priority_level ADD VALUE 'CRITICAL';
    UPDATE tickets SET priority = 'CRITICAL' WHERE severity > 9; -- 55P04!
END;
$$;
Enter fullscreen mode Exit fullscreen mode 
-- ✅ Run ALTER TYPE outside and before the DO block
ALTER TYPE priority_level ADD VALUE IF NOT EXISTS 'CRITICAL';

-- After the above has committed:
DO $$
BEGIN
    UPDATE tickets SET priority = 'CRITICAL' WHERE severity > 9;
END;
$$;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  • Always commit ALTER TYPE ... ADD VALUE in its own transaction before using the new value anywhere.
  • Use IF NOT EXISTS to make scripts idempotent and safe to re-run.
  • In Flyway, set executeInTransaction=false on migration files that contain ALTER TYPE ... ADD VALUE. 
-- Inspect current ENUM values before making changes
SELECT enumlabel, enumsortorder
FROM pg_enum
JOIN pg_type ON pg_type.oid = pg_enum.enumtypid
WHERE pg_type.typname = 'order_status'
ORDER BY enumsortorder;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Enforce a migration policy: Document a team rule that ENUM modifications must always live in their own dedicated migration file, separated from any DML that uses the new value. Add this to your code review checklist.

  2. Consider lookup tables over ENUMs: If your status or category values change frequently, a foreign-key-backed reference table is far more flexible. You can add, remove, or update values with plain INSERT/UPDATE/DELETE — no transaction restrictions, no DDL headaches.

-- Flexible alternative to ENUM
CREATE TABLE order_statuses (
    id   SERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

ALTER TABLE orders
    ADD COLUMN status_id INT REFERENCES order_statuses(id);

-- Adding a new "status" is just a simple INSERT — no DDL required
INSERT INTO order_statuses (name) VALUES ('PENDING_REVIEW');
Enter fullscreen mode Exit fullscreen mode

Following these two practices will eliminate 55P04 errors from your production deployments entirely.

📖 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.

Top comments (0)