ยท 1 min read
Change a Postgres enum used by Sequelize
Adding a value is one statement. Removing or reordering values means rebuilding the type, and the table with it.
ALTER TYPE ... ADD VALUE appends to an enum. Postgres has no DROP VALUE and no RENAME VALUE that reorders, so any other change becomes the same six-step dance: build a new type, copy the rows, drop the old one.
-- 1. move the current type out of the way
ALTER TYPE order_status RENAME TO order_status_old;
-- 2. create the type you actually want
CREATE TYPE order_status AS ENUM ('pending', 'paid', 'cancelled', 'refunded');
-- 3. rename the column so the old values stay reachable
ALTER TABLE orders RENAME COLUMN status TO status_old;
-- 4. add the column with the new type
ALTER TABLE orders ADD COLUMN status order_status NOT NULL DEFAULT 'pending';
-- 5. copy, going through text so Postgres can cast
UPDATE orders SET status = status_old::text::order_status;
-- 6. clean up
ALTER TABLE orders DROP COLUMN status_old;
DROP TYPE order_status_old;
Wrap it in a transaction
BEGIN;
-- steps 1 to 6
COMMIT;
The UPDATE rewrites every row, and ALTER TABLE takes an exclusive lock. On a large table this is a maintenance-window operation, not something to run on a busy table at noon.
When you only add a value
ALTER TYPE order_status ADD VALUE 'refunded';
Before Postgres 12 this could not run inside a transaction block, which is why older migration tooling had to disable transactions for this one migration. From 12 on it is allowed, but the new value still cannot be used in the same transaction that added it.
Watch the defaults
Step 4 gives the new column a default and NOT NULL. If the old column was nullable, rows with NULL fail the cast in step 5 โ handle them explicitly before the copy, or the migration stops halfway through.