PostgreSQL Enum Migration Generator

Paste the declaration into the pane on the left, or a whole dump with it buried somewhere inside; the first CREATE TYPE ... AS ENUM in the text becomes the list below. Type over a value to rename it, press the cross to take one out, add one with the row at the bottom. The arrows move a value, which is how a new label lands in the middle of the ordering rather than on the end.

Under the list go the columns that store the type, one per line, written the way a column is declared, like orders.status NOT NULL DEFAULT 'pending'. When you do not know what those are, the Check tab holds a catalog query that writes the lines for you.

Migration is the script to run. Read Rollback before you do, since it is the only place this page says what your change will not undo.

ALTER TYPE order_status ADD VALUE 'refunded' is the whole of it, and the label goes on the end of the ordering. With IF NOT EXISTS in front of the label, a value already present produces a notice instead of an error, which is what a migration that may run twice wants; without it the server answers enum label "refunded" already exists. The generator writes the guard while the IF NOT EXISTS option is selected and leaves it out under Plain.

Postgres alter enum add value has a second form for the middle of the list, ADD VALUE 'on_hold' BEFORE 'paid' or AFTER 'shipped', and the generator reaches for it on its own. Move the new row with the arrows and the statement comes out anchored to whichever neighbour you left it beside. On PostgreSQL 18.3, a type declared as pending, paid, shipped read pending, on_hold, paid, shipped, cancelled after one BEFORE 'paid' and one AFTER 'shipped'. The manual notes that comparisons involving a value placed anywhere but the end can be slightly slower than comparisons among the original members, which is the reason the generator writes the clause only when the position calls for it.

ALTER TYPE order_status ADD VALUE IF NOT EXISTS 'on_hold' BEFORE 'paid';

None of this touches the tables, since the statement takes a lock on the type's own catalog entry and none on the tables that store it; on 18.3, pg_locks showed no lock at all on a table using the type while the ADD VALUE was open, and no row is rewritten, so an add on a table of a hundred million rows finishes in the time a catalog update takes.

unsafe use of new value

ERROR: unsafe use of new value "refunded" of enum type order_status, SQLSTATE 55P04, with the hint that new enum values must be committed before they can be used, is what a migration gets when it adds a value and uses it inside the same transaction block. PostgreSQL 12 and later accept ADD VALUE between BEGIN and COMMIT, but the label is not usable until that COMMIT returns, so an INSERT or UPDATE carrying it on the next line fails and the whole block rolls back, new label included. PostgreSQL 11 and older refuse ADD VALUE inside a block outright.

Migration runners that wrap each file in a transaction meet this constantly. The fix is two migrations, the ADD VALUE in one and the data change in the next, or Transaction set to None on this page so that each statement commits on its own, and the comment at the top of the generated script says which of the two situations it was written for.

Of the three edits, renaming is the one that costs nothing. An enum value is a row in pg_enum that every column using the type points at, and ALTER TYPE order_status RENAME VALUE 'pending' TO 'queued' changes the text of that row while the pointers stay put, so every row that held pending reads queued afterwards without having been written. A column default declared on the old label follows it, which PostgreSQL 18.3 confirmed by reporting 'queued'::order_status as the default straight after the rename and inserting queued on the next DEFAULT VALUES. The position in the ordering does not move.

The Postgres rename enum value statement has been in the grammar since PostgreSQL 10, so a migration for anything older needs the rebuild described below. Two errors are worth recognising on sight. enum label "paid" already exists means the new name is taken, and "nope" is not an existing enum label means the old one was mistyped. The generator emits one RENAME VALUE per edited row, and the rollback it writes is the same statements the other way round.

There is no Postgres remove enum value statement. Ask for one and the server says dropping an enum value is not implemented, SQLSTATE 0A000, and the manual states the rule behind it, that existing values cannot be removed and their order cannot change short of dropping and re-creating the type. What the generator writes for a removal is that re-creation, in the order the server needs it.

BEGIN;

ALTER TYPE ticket_status RENAME TO ticket_status_old;

CREATE TYPE ticket_status AS ENUM ('open', 'closed');

UPDATE tickets SET status = 'open' WHERE status = 'pending';

ALTER TABLE tickets ALTER COLUMN status DROP DEFAULT;
ALTER TABLE tickets
  ALTER COLUMN status TYPE ticket_status
  USING status::text::ticket_status;
ALTER TABLE tickets ALTER COLUMN status SET DEFAULT 'open';

DROP TYPE ticket_status_old;

COMMIT;

Any renames come first, while the old type still owns every row, because the USING cast further down matches labels by text. Rows holding the removed label are moved to the replacement you chose beside it, or set to NULL when you left it blank, and the page warns when a NOT NULL column makes the blank impossible. The default is dropped before the type change and set again after it, since a stored default still points at the old type and is what turns the column change into default for column "status" cannot be cast automatically to type ticket_status. Reordering surviving values, which no ALTER TYPE can do, produces this same script.

The expensive line is ALTER COLUMN ... TYPE. It takes an ACCESS EXCLUSIVE lock on the table and rewrites it together with its indexes, and the lock is held until COMMIT, so on a large table the transaction wants a maintenance window. Read out of pg_locks on 18.3 just before the COMMIT above, the table showed AccessExclusiveLock granted to the session for the whole tail of the script.

A rollback brings the type back and leaves the rows as the forward script left them, because that script rewrote every row holding the removed label and nothing in the table records which rows those were; the rollback the generator writes therefore restores pending to the type, leaves those rows at open, and says so in its first comment. Copy the column somewhere first if those rows matter.

If the labels change every quarter, what you have is a lookup table wearing an enum, and a small statuses table with a foreign key into it loses a row to DELETE instead. You pay a join for that, plus one more table your seed data has to fill, which is one more thing that drifts away from the schema.