PostgreSQL Enum Migration Generator
This generator writes the SQL to add, rename or remove a value of a PostgreSQL enum type, in your browser. Adding and renaming are one ALTER TYPE each. Nothing in the grammar takes a value away, so the script that comes back rebuilds the type around whatever you kept.
- new
-- ADD VALUE runs inside a transaction block from PostgreSQL 12-- on, but the value it adds cannot be read back until this-- COMMIT returns. PostgreSQL 11 and older refuse it in a block-- at all. BEGIN; ALTER TYPE order_status ADD VALUE IF NOT EXISTS 'refunded'; COMMIT;One statement per edit, and no table is rewritten. Ctrl or Cmd with Enter copies it.
What it does and how to use it
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.
Why changing Postgres enum values is harder than it looks
An enum value is a row in pg_enum that every column using the type points at, and their order is the order the type was declared in. Renaming one costs nothing, because the rows keep pointing at the same entry while only its text changes, and a column default written on that label follows it across. ALTER TYPE ... RENAME VALUE has been in the grammar since PostgreSQL 10.
With adding, the cost is much the same and one trap is worth knowing. From PostgreSQL 12 an ALTER TYPE ... ADD VALUE may run inside a transaction block, but the value it adds cannot be used until that transaction commits, so a migration that adds refunded and then updates rows to refunded inside one BEGIN fails on the second statement. Servers older than 12 refuse the command in a block at all.
PostgreSQL never implemented the statement that would take a value out. The manual is blunt about it: existing values cannot be removed, nor their sort order changed, short of dropping and re-creating the type. The expensive line in that rebuild is ALTER TABLE ... ALTER COLUMN ... TYPE, which takes an ACCESS EXCLUSIVE lock and rewrites the table together with its indexes.
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.
Frequently asked questions
Not directly. PostgreSQL implements no ALTER TYPE ... DROP VALUE and answers 'dropping an enum value is not implemented' if you ask. What works is renaming the old type, creating a new one and moving every column across, which is what the Migration tab writes once you take a value out.
The value is not usable until the transaction that added it commits, and the server says so with 'unsafe use of new value'. Commit before the statement that reads it, or set Transaction to None so each ALTER TYPE stands alone.
Only the rebuild does. ADD VALUE and RENAME VALUE touch the catalog and leave the table alone. The ALTER COLUMN ... TYPE inside the rebuild takes an ACCESS EXCLUSIVE lock and rewrites the table and its indexes, so a big table wants a window.
Both declare the enum in their own schema file and emit the PostgreSQL DDL from it. Paste that DDL here to see what the database itself needs, then make the matching edit in schema.prisma or your Drizzle schema so the two agree.
No. The declaration is parsed and the scripts assembled by JavaScript in the tab you are reading, and this page has no endpoint to send them to. The one thing recorded is that the tool ran, with nothing you typed attached.
It is free and there is no account to make. No type definition is kept, and the page behaves exactly the same in a private window.
Run the script against a scratch database first. The playground starts a PostgreSQL server inside this tab, and the syntax checker reports where the grammar gives up if you edited the output by hand.
Other free PostgreSQL tools
- Truncate generatorPaste the DDL of a schema and it writes the TRUNCATE statement for every table in it, with RESTART IDENTITY and CASCADE where you want them. When cascading is not an option, it gives you the DELETE order instead.
- Syntax checkerRuns your statement through the grammar Postgres itself uses and reports the same message the server would, with the character position it stopped at.
- PlaygroundA real Postgres server compiled to WebAssembly, running inside the tab. Create tables, insert rows, join them, and read the plan, with no server behind it.
Further reading: Database Seed File Maintenance: Stop Patching seed.sql.