PostgreSQL migrations fail with 'unsafe use of new value' when adding enum values used as defaults

[jellologic]

Summary

When a migration adds a new enum value and that value is subsequently used (e.g., as SET DEFAULT), the migration fails with PostgreSQL's "unsafe use of new value" error. This is because Payload wraps all migration statements in a single transaction, but PostgreSQL prohibits using newly added enum values within the same transaction they were added.

Steps to Reproduce

1. Have a collection with a select field (which creates an enum)
2. Add a new option to that select field
3. Set the new option as the default value
4. Run pnpm payload migrate:create <name>
5. Run pnpm payload migrate

Error Message

error: unsafe use of new value "newvalue" of enum type public.enum_tablename_fieldname
HINT: New enum values must be committed before they can be used.

Root Cause

The generated migration contains:

ALTER TYPE "public"."enum_name" ADD VALUE 'newvalue' BEFORE 'existingvalue';
ALTER TABLE "tablename" ALTER COLUMN "fieldname" SET DEFAULT 'newvalue';

PostgreSQL's ADD VALUE for enums cannot be used in the same transaction as a statement that references the new value. Payload's migrate.ts wraps all statements in initTransaction()/commitTransaction(), so the entire migration runs in one transaction.

Related Drizzle Issue

This is tracked in Drizzle as drizzle-team/drizzle-orm#3466 and is marked as fixed in beta. However, Payload's transaction wrapping may still cause issues even after Drizzle's fix is released.

Workaround

We created a pnpm patch for @payloadcms/db-vercel-postgres that uses the DROP/RECREATE pattern instead of ADD VALUE:

1. Query existing enum values at runtime
2. Convert affected columns to TEXT
3. DROP the enum
4. CREATE the enum with all values (old + new)
5. Convert columns back to enum type
6. Set defaults

This works within a single transaction because it doesn't use ADD VALUE.

Patch Implementation

The patch modifies the sanitizeStatements function in buildCreateMigration to detect when ADD VALUE statements conflict with subsequent operations and generates safe migration code:

// Example generated migration with the patch
const enum0_result = await db.execute(sql\`
  SELECT enumlabel FROM pg_enum
  WHERE enumtypid = 'public.enum_name'::regtype
  ORDER BY enumsortorder
\`);
const enum0_existing = enum0_result.rows.map(r => r.enumlabel);
const enum0_newValues = [...enum0_existing];
enum0_newValues.push('newvalue');

await db.execute(sql\`ALTER TABLE "table" ALTER COLUMN "col" DROP DEFAULT\`);
await db.execute(sql\`ALTER TABLE "table" ALTER COLUMN "col" TYPE text USING "col"::text\`);
await db.execute(sql\`DROP TYPE IF EXISTS "public"."enum_name"\`);
await db.execute(sql.raw(\`CREATE TYPE "public"."enum_name" AS ENUM('${enum0_newValues.join("', '")}')\`));
await db.execute(sql\`ALTER TABLE "table" ALTER COLUMN "col" TYPE "public"."enum_name" USING "col"::"public"."enum_name"\`);
await db.execute(sql\`ALTER TABLE "table" ALTER COLUMN "col" SET DEFAULT 'newvalue'\`);

Environment

• Payload CMS: 3.69.0
• @payloadcms/db-vercel-postgres: 3.69.0
• PostgreSQL: 15+

Suggested Fix

Consider either:

1. Run ADD VALUE outside transaction - Detect ALTER TYPE ... ADD VALUE statements and execute them before starting the main transaction
2. Use DROP/RECREATE pattern - Implement our workaround in the core package
3. Wait for Drizzle v1 - Track the Drizzle beta fix and integrate when stable

[jellologic]

Patch File

Here's the complete pnpm patch that implements the workaround:

Gist: https://gist.github.com/jellologic/da7e51e02c658952f675b737a80ad50b

How to use:

1. Download the patch to patches/@payloadcms__db-vercel-postgres@3.69.0.patch

2. Add to your package.json:

{
  "pnpm": {
    "patchedDependencies": {
      "@payloadcms/db-vercel-postgres@3.69.0": "patches/@payloadcms__db-vercel-postgres@3.69.0.patch"
    }
  }
}

3. Run pnpm install to apply the patch

4. Now pnpm payload migrate:create <name> will generate safe migrations that work within transactions.