Enums

CREATE TYPE ... AS ENUM

Lock mode ACCESS SHARE
Risk LOW
Blocks other DDL
pgfence rule create-enum-type 
CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped');

What this does

Cheap to create, but Postgres enums are append-only: ALTER TYPE ... DROP VALUE does not exist. If a value might ever need removing, an enum forecloses that option permanently.

Safe rewrite

Prefer a lookup table for evolving categorical data; it supports DELETE and UPDATE, an enum does not

-- Option A: lookup table (recommended for evolving sets)
CREATE TABLE order_status_kinds (kind text PRIMARY KEY);
INSERT INTO order_status_kinds (kind) VALUES ('pending'), ('paid'), ('shipped');
-- Option B: CHECK constraint (works for small, stable sets)
ALTER TABLE orders ADD CONSTRAINT orders_status_check CHECK (status IN ('pending','paid','shipped'));

Read more

More in Enums

Run pgfence on your migrations ← All lock mode patterns