← 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 that is known in advance. PostgreSQL offers three ways to express this, and each has its own area of application.

Boolean is a separate story

Before talking about enumerations, it is worth dealing with 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" through 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.

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. You can add one (ALTER TYPE ... ADD VALUE), but you cannot remove one. If you want to drop an old value, you will have to recreate the type entirely together with all the tables that depend 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 REFERENCES order_status_dict(code) DEFAULT 'NEW'
);

Here each status is a row in a separate table. You can add, rename, or remove values with ordinary SQL commands, just like any data.

An additional plus: you can store any attributes next to the code. In the example — a description, a sort order, and a "terminal status" flag (meaning the order will no longer change in this status).

On the downside: each value takes up to 20 bytes instead of the 4 of an ENUM, and when selecting data you need a JOIN to the lookup table if you want to show the description.

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. If the number of values grows to 10 or more, it becomes hard to read and maintain. On top of that, there is no foreign key — another table cannot reference these values as a lookup.

How to choose

SituationChoice
The set is fixed, changes rarely, no attributesENUM
The set grows, renaming or removal is neededlookup table
Attributes are needed alongside the codelookup table
Simple, up to 5–7 values, no attributesCHECK IN
Values change often without a deploylookup table

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 'PARTIALLY_REFUNDED';

This works fast even on a large table. But there is an important detail: you cannot use the new value in the same transaction:

BEGIN;
ALTER TYPE order_status ADD VALUE 'PARTIALLY_REFUNDED';
INSERT INTO order_doc (status) VALUES ('PARTIALLY_REFUNDED');  -- error!
COMMIT;

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

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 even the slightest chance you will need to remove a value — better take a lookup table from the very start.

Mapping in the application code

Regardless of how it is stored in the database, an enumeration in the application code should be typed — not a plain string. This lets the compiler (or the linter) catch a typo, and the IDE suggest 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:
    row = await conn.execute(
        "SELECT status FROM order_doc WHERE id = %s", (order_id,)
    ).fetchone()
    status = OrderStatus(row[0])

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

A status is not a state machine

An important distinction: a type in the database only answers the question "which values are allowed". It does not manage the transitions between them.

If an order has a rule "from NEW you can move only to PAID or CANCELLED, but not straight to SHIPPED" — that is application logic, not a constraint in the database. Such rules are described separately in the domain-layer code.

In short

  • boolean for flags — not smallint 0/1 and not char 'Y'/'N'.
  • Three ways: ENUM (4 bytes, type safety), a lookup table (CRUD, attributes, a foreign key), CHECK IN (simple, without a lookup table).
  • ENUM suits a stable set; a lookup table — when values change or attributes are needed.
  • You cannot remove a value from an ENUM natively — that is a complex multi-step migration.
  • ALTER TYPE ADD VALUE and using the new value are separate migrations, not one transaction.
  • Statuses of domain entities — usually a lookup table: they grow and acquire attributes.
  • In the code an enumeration is always typed, not a plain string.
  • The rules for transitions between statuses are separate logic in the code, not a type in the database.
  • 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 in production.