Enumerations in PostgreSQL: ENUM, lookup table, or CHECK

← Back to the section

An order status, a notification type, a user role, a currency — these are all enumerable values: the field takes exactly one option out of a set known in advance. PostgreSQL offers three ways to express this, each with its own area of application.

Boolean is a separate story

Before enumerations, a word about flag fields. Sometimes you see code like this:

is_active smallint NOT NULL DEFAULT 1 CHECK (is_active IN (0, 1)),
is_active char(1)  NOT NULL DEFAULT 'Y' CHECK (is_active IN ('Y', 'N'))

This is an attempt to store "yes/no" as a number or a character: that is how it was done in old databases that had no boolean type. PostgreSQL has boolean:

is_active   boolean NOT NULL DEFAULT true,
is_verified boolean NOT NULL DEFAULT false

Why this is better:

• It takes 1 byte and is an atomic type.
• The query reads naturally: WHERE is_active, not WHERE is_active = 1.
• Every language and driver understands boolean correctly without extra mapping.

Three ways to store an enumeration

Let's take a concrete example: an order status — NEW, PAID, SHIPPED, DELIVERED, CANCELLED.

Option 1: an ENUM type

CREATE TYPE order_status AS ENUM ('NEW', 'PAID', 'SHIPPED', 'DELIVERED', 'CANCELLED');

CREATE TABLE order_doc (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status order_status NOT NULL DEFAULT 'NEW'
);

PostgreSQL stores each value as 4 bytes (an internal numeric code), while in queries you work with strings. Values compare and sort in declaration order, not alphabetically: ORDER BY status starts with NEW, not with CANCELLED.

This is convenient when:

• the set of values is known in advance and changes rarely;
• there is no need to store metadata alongside (a description, a sort order, and so on);
• compact storage matters.

The main pitfall: removing a value from an ENUM natively is impossible. Adding a new one is a single command, removing an old one — there is nothing for that: you will have to recreate the type together with every table that depends on it. That is a complex migration.

Option 2: a lookup table

CREATE TABLE order_status_dict (
    code        varchar(20) PRIMARY KEY,
    description text NOT NULL,
    sort_order  smallint NOT NULL,
    is_terminal boolean NOT NULL
);

INSERT INTO order_status_dict VALUES
    ('NEW',       'Created',    10, false),
    ('PAID',      'Paid',       20, false),
    ('SHIPPED',   'Shipped',    30, false),
    ('DELIVERED', 'Delivered',  40, true),
    ('CANCELLED', 'Cancelled',  50, true);

CREATE TABLE order_doc (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status varchar(20) NOT NULL DEFAULT 'NEW' REFERENCES order_status_dict(code)
);

Each status is a row in a separate table: you can add, rename, or remove a value with ordinary SQL commands, just like any data.

An additional plus: attributes live next to the code — in the example a description, a sort order, and a "terminal status" flag (in it the order no longer changes).

On the downside: the column holds the string itself — 'DELIVERED' is nine characters plus a length byte, 10 bytes against the 4 of an ENUM, and showing the description needs a JOIN to the lookup table.

Option 3: text + CHECK

CREATE TABLE order_doc (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status text NOT NULL DEFAULT 'NEW'
        CHECK (status IN ('NEW', 'PAID', 'SHIPPED', 'DELIVERED', 'CANCELLED'))
);

The simplest option: no types, no lookup tables. The constraint sits right in the table definition.

It suits a small, stable set (up to 5–7 values), when a lookup table feels excessive. At ten values the list in the DDL is already hard to read, and there is no foreign key here — another table cannot reference this set.

How to choose

Start with the column you already have — here it is the status in the orders table: the set of values in real data is almost always wider than the one in your head.

live example

SELECT status, count(*) AS cnt
FROM orders
GROUP BY status
ORDER BY cnt DESC;

If PAID and paid sit side by side, or a forgotten status shows up, it is too early to move that set into an ENUM: clean the data first.

A practical rule:

• Statuses of domain entities (order_status, payment_status) — almost always a lookup table. Over time attributes get added to them, and old values sometimes need to be removed.
• Technical enumerations (event_kind, notification_channel) — an ENUM or a CHECK, if the set is settled.
• Standard codes (country by ISO 3166, currency by ISO 4217, language by ISO 639) — a lookup table with standard values.

Adding and removing ENUM values

If you did choose an ENUM and need to add a new value:

ALTER TYPE order_status ADD VALUE 'REFUNDED';

This works fast even on a large table: the data is not rewritten. The new value lands at the end; another place is set with BEFORE or AFTER:

ALTER TYPE order_status ADD VALUE 'REFUNDED' AFTER 'PAID';

But there is an important detail: you cannot use the new value in the same transaction:

BEGIN;
ALTER TYPE order_status ADD VALUE 'REFUNDED';
INSERT INTO order_doc (status) VALUES ('REFUNDED');  -- unsafe use of new value
COMMIT;

The right way — split adding the value and inserting the data across separate migrations: first the deploy, then the use.

Rename a value (PostgreSQL 10+):

ALTER TYPE order_status RENAME VALUE 'OLD_NAME' TO 'NEW_NAME';

Removing a value — there is no native way. If you need it, you will have to:

1. Create a new type with the desired set of values.
2. Add a temporary column with the new type.
3. Migrate the data (old values → new ones).
4. Drop the old column and rename the new one.
5. Drop the old type.

That is several migrations with an application deploy between them. If there is any chance you will need to remove a value, take a lookup table right away.

Mapping in the application code

Regardless of how it is stored, an enumeration in the code should be typed — not a plain string. Then the compiler (or the linter) catches a typo, and the IDE suggests the allowed values.

// jOOQ generates a Java enum from a PG ENUM automatically.
// For a lookup table (a varchar column) a manual converter is needed.
public enum OrderStatus { NEW, PAID, SHIPPED, DELIVERED, CANCELLED }

// Reading the status
OrderStatus status = dsl
    .select(ORDER_DOC.STATUS)
    .from(ORDER_DOC)
    .where(ORDER_DOC.ID.eq(orderId))
    .fetchOne(r -> r.get(ORDER_DOC.STATUS));

// Updating — the enum is passed directly
dsl.update(ORDER_DOC)
    .set(ORDER_DOC.STATUS, OrderStatus.PAID)
    .where(ORDER_DOC.ID.eq(orderId))
    .execute();

type OrderStatus string

const (
    OrderStatusNew       OrderStatus = "NEW"
    OrderStatusPaid      OrderStatus = "PAID"
    OrderStatusShipped   OrderStatus = "SHIPPED"
    OrderStatusDelivered OrderStatus = "DELIVERED"
    OrderStatusCancelled OrderStatus = "CANCELLED"
)

// pgx v5: Scan straight into OrderStatus
var status OrderStatus
err := row.Scan(&status)

// Writing
_, err = pool.Exec(ctx,
    "UPDATE order_doc SET status = $1 WHERE id = $2",
    OrderStatusPaid, id,
)

enum OrderStatus {
    NEW       = "NEW",
    PAID      = "PAID",
    SHIPPED   = "SHIPPED",
    DELIVERED = "DELIVERED",
    CANCELLED = "CANCELLED",
}

// node-postgres returns a string — explicit cast
const { rows } = await pool.query<{ status: string }>(
    "SELECT status FROM order_doc WHERE id = $1",
    [id],
);
const status = rows[0].status as OrderStatus;

// Writing
await pool.query(
    "UPDATE order_doc SET status = $1 WHERE id = $2",
    [OrderStatus.PAID, id],
);

from enum import Enum
import psycopg

class OrderStatus(str, Enum):
    NEW       = "NEW"
    PAID      = "PAID"
    SHIPPED   = "SHIPPED"
    DELIVERED = "DELIVERED"
    CANCELLED = "CANCELLED"

async with await psycopg.AsyncConnection.connect(dsn) as conn:
    cur = await conn.execute(
        "SELECT status FROM order_doc WHERE id = %s", (order_id,)
    )
    row = await cur.fetchone()
    status = OrderStatus(row[0])

    await conn.execute(
        "UPDATE order_doc SET status = %s WHERE id = %s",
        (status.value, order_id),
    )

What typing buys you is visible without a database:

live example

public class OrderStatusDemo {
    enum OrderStatus { NEW, PAID, SHIPPED, DELIVERED, CANCELLED }

    public static void main(String[] args) {
        OrderStatus status = OrderStatus.valueOf("PAID");
        System.out.println(status.name() + ": ordinal " + status.ordinal());
        try {
            OrderStatus.valueOf("PAYED");
        } catch (IllegalArgumentException e) {
            System.out.println("the typo never reaches the database: " + e.getMessage());
        }
    }
}

valueOf accepts only declared names: a string with a typo fails at the border with the database instead of becoming an unknown status inside the logic. The second thing in the output is ordinal: the number depends on the declaration order. That is why the column holds the name, not the number — insert a value in the middle and every number shifts, while the data in the database stays as it was.

A status is not a state machine

A type in the database only answers the question "which values are allowed". It does not manage the transitions between them.

The rule "from NEW you can move only to PAID or CANCELLED, but not straight to SHIPPED" is application logic, not a constraint in the database: such rules live in the domain-layer code.

In short

• boolean for flags — not smallint 0/1 and not char 'Y'/'N'.
• Three ways: ENUM (4 bytes, typing), a lookup table (INSERT/DELETE, attributes, a foreign key), CHECK IN (simple, up to 5–7 values).
• A value can be added to an ENUM but not removed: removal is a multi-step migration that recreates the type and the column.
• ALTER TYPE ADD VALUE and using the new value are separate migrations, not one transaction; an ENUM sorts in declaration order.
• Statuses of domain entities — usually a lookup table: they grow and acquire attributes; technical enumerations — an ENUM or a CHECK.
• In the code an enumeration is typed, and the column holds the name, not the number; transition rules live in the code, not in the type.

What to read next

• Numbers and precision in PostgreSQL — the specifics of numeric types.
• String types in PostgreSQL — varchar for lookup codes.
• JSONB — when it is justified, when it is not — for complex structures.
• Zero-downtime migrations — how to safely change types on a live database.