Adding and Altering Enum Values Safely

A new order state, partially_refunded, needs to exist in the database before the feature that uses it ships. On PostgreSQL the order_status enum type accepts it with one ALTER TYPE; on MySQL the orders.status ENUM(...) column accepts it with one ALTER TABLE ... MODIFY. Either can be instant — or it can rewrite a 200-million-row table, or it can break the old application version that is still running and does not know what partially_refunded means. Enums are a type change in miniature: the storage rules decide whether the change is free, and the rollout order decides whether it is safe. This guide covers adding, reordering, renaming and removing values on both engines, and the release sequence that keeps old and new code working. It belongs to Changing Column Types Safely; the idempotency side of PostgreSQL enums is covered in idempotent enum and type changes in Postgres.

Enum Changes by Engine Matrix of enum operations in PostgreSQL enum types and MySQL ENUM columns, with their cost. Adding a value is instant in PostgreSQL anywhere in the order; in MySQL it is instant only when appended at the end without changing storage size. Reordering or inserting in the middle in MySQL requires a table copy. Renaming is a catalog change in PostgreSQL 10+ and a copy in MySQL. Removing values is unsupported in PostgreSQL and a copy in MySQL. Enum Changes by Engine Operation PostgreSQL enum type MySQL ENUM column add value at end ADD VALUE, instant INSTANT (same storage size) add value in the middle ADD VALUE BEFORE/AFTER, instant COPY rename a value RENAME VALUE (PG 10+), instant COPY + data rewrite remove a value not supported: new type + swap COPY grow past 255 members no limit concern COPY (1 → 2 bytes)
PostgreSQL enums are cheap to extend anywhere; MySQL ENUM columns are cheap to extend only at the end.

Symptom / Error Signatures

Enum changes fail or hurt in a few specific ways:

ERROR:  unsafe use of new value "partially_refunded" of enum type order_status        -- PG 12+, SQLSTATE 55P04
ERROR:  ALTER TYPE ... ADD VALUE cannot run inside a transaction block                  -- PG 11 and earlier
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: ... Try ALGORITHM=COPY.  -- MySQL, value not appended at end
ERROR 1265 (01000): Data truncated for column 'status' at row 1                         -- MySQL, value removed while rows use it

And from application code after the migration: an old version reading a row with the new value fails to deserialise it (ValueError: 'partially_refunded' is not a valid OrderStatus in Python, an unknown enum constant exception in Java), because the database accepted a value the running code has never heard of.

Root Cause Analysis

PostgreSQL stores enum values in rows as four-byte OIDs that point at entries in pg_enum, each with a floating-point sort order. Adding a value inserts a pg_enum row — anywhere in the order, using BEFORE or AFTER — without touching any table. Renaming a value (PostgreSQL 10+) changes only the label in pg_enum. Removing a value is not supported, because rows might reference it and there is no cheap way to check. Since PostgreSQL 12, ADD VALUE may run inside a transaction, but the new value cannot be used until that transaction commits.

MySQL stores an ENUM column value as the index of the member in the column definition (1 byte for up to 255 members, 2 bytes beyond). Appending members at the end keeps every existing index valid, so MySQL 8.0 can apply it instantly as long as the storage size does not change. Inserting a member in the middle, reordering, or renaming shifts or changes the meaning of stored indexes, so the table must be copied.

Whatever the storage cost, the application risk is the same on both engines: old code must never read a value it cannot handle. The safe order is to ship code that tolerates the new value first, then add it to the database, then ship code that writes it.

Release Order for a New Enum Value Timeline across three releases. Release 1 deploys code that can read partially_refunded without failing, but never writes it. The migration adds the value to the database after release 1 is fully rolled out. Release 2 deploys code that writes the new value. Old code never encounters a value it cannot read. Release Order for a New Enum Value ADD VALUE migration Release 1: tolerant readers can read the new value Schema value exists Release 2: writers writes partially_refunded tolerant code database accepts value writes value
Readers first, schema second, writers third — the enum version of expand and contract.

Immediate Mitigation

1. On PostgreSQL, add the value in its own migration.

-- PostgreSQL 12+ · migration role · catalog change, no table lock
-- WARNING: do not use the new value anywhere else in this migration (55P04 until commit).
ALTER TYPE order_status ADD VALUE IF NOT EXISTS 'partially_refunded' AFTER 'refunded';
-- ROLLBACK PATH: none — values cannot be dropped; leave it unused if the feature is abandoned.

2. On MySQL, append at the end and demand the instant algorithm. Restate the full existing list, unchanged, followed by the new member.

-- MySQL 8.0 · migration session · fails fast if the change would copy the table
SET SESSION lock_wait_timeout = 5;
ALTER TABLE orders
  MODIFY status ENUM('pending','paid','shipped','refunded','partially_refunded') NOT NULL DEFAULT 'pending',
  ALGORITHM=INSTANT;
-- ROLLBACK PATH: removing the member requires a copy and fails if rows use it; prefer leaving it unused.

3. If old code is failing on the new value, stop the writers, not the schema. Roll back or feature-flag the code that writes the value; the value’s presence in the type is harmless until a row uses it.

Permanent Fix / Long-Term Pattern

Treat enum changes as three-step releases: tolerant readers, schema, writers. Make readers tolerant by design — map unknown values to an explicit “unknown” state rather than throwing — so that future additions only need the last two steps. In MySQL, always append at the end and include ALGORITHM=INSTANT, so a change that would copy the table fails in CI instead of in production.

Removing or renaming values is a contract step. On PostgreSQL, renaming with ALTER TYPE ... RENAME VALUE is instant but changes the label every reader sees at once, so treat it like a column rename and stage it — or avoid it by adding the new value, migrating rows in batches, and leaving the old value unused. To truly remove values, create a new type without them, convert the column with the shadow-column method from converting a column type with a shadow column, and drop the old type. If a domain changes often, a lookup table with a foreign key or a text column with a CHECK constraint is easier to evolve online, as discussed in Adding Constraints Without Downtime. Coordinating writers with feature flags follows using feature flags to toggle schema changes safely.

Changing an Existing Enum Decision tree. Adding a value: on PostgreSQL use ADD VALUE; on MySQL append at the end with ALGORITHM=INSTANT. Removing or renaming a value: if rows still use the old value, migrate rows first in batches; then leave the old value unused, or on PostgreSQL build a new type and swap the column. Changing an Existing Enum Adding a value? yes no PostgreSQL? yes no ADD VALUE IF NOT EXISTS, own migration Append at end, ALGORITHM=INSTANT Remove/rename: migrate rows, then new type or leave unused
Additions are cheap on both engines if done right; removals are always a migration project.

ORMs deserve a specific check. Prisma, SQLAlchemy, TypeORM and Rails all map database enums to language-level enums, and their generated migrations for a new value are usually correct for the database but say nothing about the running code. Review the application’s deserialisation path — the model mapping, the API schema, any switch statements without a default branch — as part of the same pull request that adds the value to the schema.

Verification Checklist

Frequently Asked Questions

Is adding a PostgreSQL enum value in the middle of the order expensive? No. ADD VALUE ... BEFORE or AFTER assigns a fractional sort position in pg_enum; no table is rewritten.

Why must MySQL ENUM additions go at the end? MySQL stores each value as the member’s position in the list. Appending keeps existing positions valid, so no data changes; inserting in the middle shifts positions and requires rewriting every row.

Can I remove a PostgreSQL enum value? Not directly. Migrate rows off the value, then either leave it unused or create a new type without it and convert the column, which is a rewrite handled with the shadow-column technique.

How do I protect old code from new values? Deploy code that tolerates unknown values — mapping them to a safe default or “unknown” state — before adding them to the database, and only then deploy code that writes them.

Related Articles