Postgres enum change with Schema Diff Studio
Two enums are loaded before and after a release: order_status gains on_hold and refunded in specific positions, and plan_tier loses legacy and gains team. The comparison runs on load. New values become ALTER TYPE ... ADD VALUE with BEFORE/AFTER placed outside the transaction; the removed value triggers a type rebuild flagged for review, and both paths are verified in Postgres in your tab.
- Paste the current CREATE TYPE ... AS ENUM statements and the tables that use them into Before; pg_dump --schema-only prints both.
- Edit the value lists in After in the order you want them sorted. Order matters: enums sort by declared position, not alphabetically.
- Press Compare. Values that are only added become ALTER TYPE ... ADD VALUE 'x' AFTER 'y', placed before BEGIN. A removed or reordered value shows as "Recreate enum", flagged REVIEW.
- Before running a rebuild in production, update the rows that still hold the removed value (UPDATE accounts SET plan = 'pro' WHERE plan = 'legacy'); the column conversion fails otherwise.
- Check Verified on the Migration tab and copy the up script. Downloading the up and down .sql files and the full rollback script are Pro.
What to know
Postgres can add a value to an enum in place with ALTER TYPE order_status ADD VALUE 'refunded' AFTER 'shipped'; BEFORE and AFTER control where it sorts, and IF NOT EXISTS makes the statement safe to re-run. Before Postgres 12 that statement could not run inside a transaction block at all. Since 12 it can, but the new value cannot be used until the transaction that added it commits, so a migration that adds a value and then sets a default or inserts rows with it fails. This tool therefore writes ADD VALUE lines ahead of BEGIN, where each one commits on its own.
There is no ALTER TYPE ... DROP VALUE, and the values cannot be reordered in place; ALTER TYPE ... RENAME VALUE (Postgres 10+) is the only other in-place change. To remove legacy from plan_tier, the type has to be rebuilt: rename the old type to plan_tier__old, create plan_tier with the new list, convert every column with ALTER COLUMN plan TYPE plan_tier USING plan::text::plan_tier, restore its default, then drop the old type. The cast through text is what makes the conversion work, and it is also where it fails: any row still holding 'legacy' has no matching value in the new type.
The conversion is an ALTER COLUMN ... TYPE, so it rewrites the table and holds an ACCESS EXCLUSIVE lock while it does. On a large table, schedule it, or avoid it: if the old value only needs to stop being chosen, a CHECK (plan <> 'legacy') constraint added NOT VALID and then validated achieves that without a rewrite. Deleting the row from pg_enum by hand is sometimes suggested; it is unsupported and can leave the value referenced from index pages, so the rebuild is the safe path.
Defaults, views and functions that mention the enum depend on it. The generated script drops and restores the column default around the conversion, and verification runs the whole script against your Before schema and compares the result with After, so a dependency that would block DROP TYPE shows up here rather than in production.
Updated · Cosmovex
Questions
Why are the ADD VALUE statements outside BEGIN/COMMIT?
A value added inside a transaction cannot be used until that transaction commits (and before Postgres 12, ADD VALUE could not run in a transaction block at all). Running each ADD VALUE first, on its own, means the rest of the migration can already use the new values in defaults, checks or data updates.
How do I remove a value from a Postgres enum?
Rebuild the type: rename the old type, create the new one without the value, convert each column with USING col::text::new_type, then drop the old type. Update or delete rows that hold the removed value first, or the conversion fails. The tool writes this sequence for you and flags it for review.
Can I put a new value in a specific position?
Yes. Write the list in After in the order you want. The migration uses ADD VALUE ... BEFORE or AFTER so the new value sorts where you placed it, which matters for ORDER BY status and for comparisons such as status < 'shipped'.
Does adding an enum value lock or rewrite the table?
No. ADD VALUE only changes the type in the catalog; tables that use the enum are not rewritten. Removing or reordering values is different, because every column of that type is converted, which rewrites those tables.
Should I use a lookup table or a CHECK constraint instead of an enum?
If values change often, a text column with a CHECK (status IN (...)) constraint or a small lookup table with a foreign key is easier to change later: both can be altered without rebuilding a type. Enums are compact and self-documenting. This tool compares all three forms, so you can see what each change costs.
The free plan covers everything on this page. Schema Diff Studio Pro ($29, paid once) is described on the Schema Diff Studio page.
