In PostgreSQL, how do you add a new value to an enum type like order_status, and what can you not do to it afterwards?
answer
- ALTER TYPE, not ALTER TABLE
- placement relative to a neighbour
- the new label waits for commit
- rename yes, remove no
- pg_enum keeps the sort order
basics
~20 sALTER TYPE order_status ADD VALUE 'refunded' adds a label, optionally BEFORE or AFTER another, without rewriting any table; inside a transaction block it is unusable until commit. Labels can be renamed but not removed or reordered.
solid answer
~50 s`ALTER TYPE order_status ADD VALUE [IF NOT EXISTS] 'refunded' [BEFORE | AFTER 'shipped']` appends a label, or places it next to a neighbour, without touching table data. Order matters because enums sort in declaration order, not alphabetically, so `ORDER BY status` and `status > 'paid'` follow that list. If the `ADD VALUE` on an existing type runs inside a transaction block, using the new label before commit fails with SQLSTATE `55P04`, `unsafe use of new value`. `RENAME VALUE` changes a label in place. There is no way to drop a label or change the order: you create a new type and convert each column with `ALTER COLUMN ... TYPE ... USING status::text::new_type`, which rewrites the table. That is why a status set that changes often is better as a lookup table or text with a CHECK.
code
sql · 13 linesCREATE TYPE order_status AS ENUM ('placed', 'paid', 'shipped', 'delivered');
ALTER TYPE order_status ADD VALUE IF NOT EXISTS 'refunded';
ALTER TYPE order_status ADD VALUE 'packed' BEFORE 'shipped';
SELECT enum_range(NULL::order_status);
-- {placed,paid,packed,shipped,delivered,refunded}
BEGIN;
ALTER TYPE order_status ADD VALUE 'cancelled';
UPDATE ingredient_order SET status = 'cancelled' WHERE id = 42;
-- ERROR: unsafe use of new value "cancelled" of enum type order_status
ROLLBACK;go deeper
Know how to create an enum, add a label with ALTER TYPE ... ADD VALUE, and that labels sort in declaration order.
Explain BEFORE/AFTER placement, why a new label is unusable until commit (55P04), and that removal or reordering means replacing the type.
Plan enum migrations around these limits: separate commits for ADD VALUE, and a table rewrite when retiring labels on large tables.
Choose enum, lookup table or CHECK by how often the set changes and who owns it, and make that a schema convention.
## What a PostgreSQL enum is An **enumerated type** is a static, ordered set of labels created with `CREATE TYPE order_status AS ENUM ('placed', 'paid', 'shipped', 'delivered')`. A few properties shape everything else: - Each value takes **4 bytes** on disk, whatever the label length. - Labels are **case-sensitive** and whitespace counts, so `'Paid'` is not `'paid'`. A label is limited by `NAMEDATALEN`, 63 bytes in standard builds. - The label-to-value mapping lives in the catalog **`pg_enum`** (`enumtypid`, `enumsortorder`, `enumlabel`). - Each enum is its own type: comparing `order_status` with another enum, or with a `text` column, needs an explicit cast. A quoted literal such as `'paid'` is fine, because it is read as the enum. ## Ordering is declaration order Comparison operators and `ORDER BY` follow the order the labels were declared, not the alphabet. With the type above, `status > 'paid'` means shipped or delivered, `MIN(status)` is the earliest stage, and `enum_range(NULL::order_status)` lists the labels in order. This is the feature that makes an order-status enum attractive, and also why insertion position matters when you add a label. ## Adding a value: ALTER TYPE ... ADD VALUE `ALTER TYPE order_status ADD VALUE 'refunded'` appends a label at the end. `BEFORE 'shipped'` or `AFTER 'paid'` puts it elsewhere in the order, and `IF NOT EXISTS` turns a duplicate into a notice rather than an error. No table is rewritten: existing rows still hold the same 4-byte values. Two details matter in production: - A label inserted in the middle gets a fractional `enumsortorder`, and comparisons that involve it can be slightly slower than comparisons among the original labels. The manual calls the slowdown usually insignificant. - Migration tools that wrap every migration in a transaction meet the next rule. ## The transaction-block trap `ADD VALUE` may run inside a transaction block, but the new label **cannot be used until that transaction commits**. Any use of it, such as an insert or a cast, fails with: `ERROR: unsafe use of new value "cancelled" of enum type order_status` (SQLSTATE `55P04`), with the hint that new enum values must be committed before they can be used. The fix is to put the `ALTER TYPE` in its own migration step, commit it, and use the label in a later transaction. The one exception is a type created in the same transaction: outside a savepoint, its labels, including ones added afterwards, can be used at once, because the whole type would vanish with a rollback anyway. `enum_first(NULL::order_status)` and `enum_last(NULL::order_status)` return the ends of the order, which saves hard-coding the initial status in application code. ## What you cannot do: remove or reorder `ALTER TYPE` has `ADD VALUE` and `RENAME VALUE` for enums and nothing else. The manual is explicit that existing values cannot be removed and their sort order cannot be changed short of dropping and recreating the type. Retiring a label therefore means replacing the type: 1. Make sure no row uses the label you are retiring. 2. `CREATE TYPE order_status_v2 AS ENUM (...)` with the labels you keep, in the order you want. 3. For every column, drop its default, then `ALTER TABLE ... ALTER COLUMN status TYPE order_status_v2 USING status::text::order_status_v2`, and set the default again. 4. Update functions and views that name the old type, drop it, and rename the new one. Step 3 rewrites each table under a heavy lock, so on a large orders table this is a planned change, not a quick migration. ## Enum, lookup table or text with CHECK | Concern | Enum | Lookup table + FK | `text` + `CHECK` | |---|---|---|---| | Storage per row | 4 bytes | the key's size | the label's length | | Built-in ordering | declaration order | a sort column you add | alphabetical unless you add one | | Add a value | `ALTER TYPE ... ADD VALUE` | `INSERT` | replace the constraint | | Remove a value | replace the type | `DELETE` | replace the constraint | | Extra attributes per value | none | any columns | none | An enum suits a small, stable set where order carries meaning. A status set that product owners extend every sprint, or that needs display names and flags such as "is terminal", belongs in a lookup table.
- How do you retire the 'packed' label once no order uses it?There is no `DROP VALUE`. Create `order_status_v2` without it, then for each column drop the default, run `ALTER COLUMN status TYPE order_status_v2 USING status::text::order_status_v2`, and restore the default. Fix functions and views that name the old type, drop it and rename the new one. Each column change rewrites its table under a heavy lock.
- Can you compare an order_status column with a text column directly?No. PostgreSQL has no implicit cast from `text` to an enum, so `o.status = s.status_text` fails because no such operator exists. Cast one side explicitly, for example `o.status::text = s.status_text`. A quoted literal such as `'paid'` works because it is read as the enum type.
- When would you choose a lookup table over an enum for order status?When labels change often or must be retired, when each status needs attributes such as a display name or an is-terminal flag, or when non-developers manage the list. A lookup table changes with ordinary `INSERT` and `DELETE`. An enum wins for a small, stable set: 4 bytes per row, type safety and built-in ordering.
- ALTER TYPE ... DROP VALUE removes an enum label that no row uses.
- Enum values sort alphabetically, so ORDER BY status puts delivered before paid.
- ALTER TYPE ... ADD VALUE rewrites every table that uses the enum.
- A label added to an existing enum inside BEGIN ... COMMIT can be used straight away in that transaction.
- Enum labels are case-insensitive, so 'Paid' matches 'paid'.