gotchasqlpostgresqlModerate
Enum types: pros, cons, and the ALTER TABLE trap
Viewed 0 times
enum typeALTER TYPE ADD VALUEenum migrationCHECK constraintpg_enumschema migration
Error Messages
ERROR: cannot drop type order_status because other objects depend on itERROR: invalid input value for enum order_status: "refunded"Problem
PostgreSQL enum types seem convenient for status columns, but adding a new value to an enum mid-list requires a table rewrite in older versions, and removing values is impossible without recreating the type.
Solution
Use ADD VALUE to append to an enum safely, or prefer CHECK constraints or a lookup table:
-- Safe: add a new value (PG 9.1+, no rewrite needed if appending):
ALTER TYPE order_status ADD VALUE 'refunded' AFTER 'cancelled';
-- DANGER: cannot remove enum values or reorder:
-- Must recreate the type to do so:
ALTER TABLE orders ALTER COLUMN status TYPE text;
DROP TYPE order_status;
CREATE TYPE order_status AS ENUM ('pending','processing','shipped','refunded');
ALTER TABLE orders ALTER COLUMN status TYPE order_status
USING status::order_status;
-- Alternative: CHECK constraint (easier to modify):
ALTER TABLE orders ADD CONSTRAINT chk_status
CHECK (status IN ('pending','processing','shipped','cancelled'));
-- Safe: add a new value (PG 9.1+, no rewrite needed if appending):
ALTER TYPE order_status ADD VALUE 'refunded' AFTER 'cancelled';
-- DANGER: cannot remove enum values or reorder:
-- Must recreate the type to do so:
ALTER TABLE orders ALTER COLUMN status TYPE text;
DROP TYPE order_status;
CREATE TYPE order_status AS ENUM ('pending','processing','shipped','refunded');
ALTER TABLE orders ALTER COLUMN status TYPE order_status
USING status::order_status;
-- Alternative: CHECK constraint (easier to modify):
ALTER TABLE orders ADD CONSTRAINT chk_status
CHECK (status IN ('pending','processing','shipped','cancelled'));
Why
Enum values are stored by OID in pg_enum. Adding a value is a catalog-only operation. Removing one would break existing rows that reference the OID. CHECK constraints are just text comparisons and can be dropped and recreated freely.
Gotchas
- ALTER TYPE ... ADD VALUE cannot be rolled back inside a transaction (it auto-commits the catalog change in PG < 14)
- Enum comparisons use the declared sort order, not alphabetical; this can surprise ORDER BY queries
- ORMs may cache enum values; restart app servers after altering an enum
- Enums across schemas can collide; qualify with schema name in shared libraries
Code Snippets
Inspect all enum types and their ordered values
-- List all enum types and their values:
SELECT t.typname, e.enumlabel, e.enumsortorder
FROM pg_type t
JOIN pg_enum e ON t.oid = e.enumtypid
WHERE t.typtype = 'e'
ORDER BY t.typname, e.enumsortorder;Context
Designing status or category columns in PostgreSQL where values evolve over time
Revisions (0)
No revisions yet.