Topic 10

Arrays, Ranges, Enums, and Domains

Native Types

Most engines have none of the four types in this topic, and each one replaces something people otherwise build by hand: an array instead of a side table for tags, a range instead of a start and an end column, an enum instead of a lookup table, a domain instead of the same CHECK copied onto nine tables. Each also has a failure mode that arrives about a year after the decision.

Cartwheel's courier_shifts table records shift assignments — one row per courier per shift — and today it holds a shift_start and a shift_end, with the "is this courier already booked" test living in the dispatch service. That test uses < where it needs <=, and the only case it gets wrong is a shift beginning at the exact minute another ends, so no ticket has ever been filed against it. The fix in this topic does not correct the comparison. It removes the place where a comparison could be written.

Four types, and the hand-built thing each one replaces

A side table for tags — a small, unordered set of labels read with the row→An array

A start column and an end column, plus the comparison that decides which bound belongs→A range

A lookup table for a small label set the code owns→An enum

The same CHECK copied onto nine tables→A domain

Arrays

Any Postgres type can be an array, including a type an extension added an hour ago. Arrays are 1-indexed, they compare and sort as values, and they have their own operators: = ANY(...) for membership, @> for containment, unnest() to turn one back into rows, and GIN for indexing.

A set stored inline, with the index that makes it searchable

ALTER TABLE products ADD COLUMN tags text[] NOT NULL DEFAULT '{}';

UPDATE products SET tags = '{organic,vegan,chilled}' WHERE id = 4471;

SELECT id FROM products WHERE tags @> ARRAY['organic'];
SELECT id FROM products WHERE 'vegan' = ANY(tags);

CREATE INDEX products_tags_gin ON products USING gin (tags);

That is the good case: a small, unordered set of attribute-free labels, read on the same page as the row it belongs to, with no join and no second table to keep in step. The trap is what an array element cannot do. It cannot carry a foreign key, so nothing stops a tag that refers to nothing. It cannot have columns of its own — replacing order_items with an array of product ids on orders would give up referential integrity, per-line quantity and unit price in a single move. And unnest() produces a row estimate the planner is poor at, so an aggregate over unnested arrays plans badly at scale.

Ranges

A range type stores two bounds and their inclusivity as one value. Postgres ships int4range, int8range, numrange, tsrange, tstzrange and daterange, with operators for overlap (&&), containment (@>) and adjacency (-|-). One column replaces two, and the questions people ask about periods become single operators.

Two columns become one value that knows its own boundaries

ALTER TABLE courier_shifts ADD COLUMN shift tstzrange;

UPDATE courier_shifts
   SET shift = tstzrange(shift_start, shift_end, '[)');

SELECT courier_id FROM courier_shifts
 WHERE shift && tstzrange(now(), now() + interval '2 hours');

One operator, &&, replaces a comparison of two bounds against two other bounds in four combinations. The third argument is the part that hand-rolled columns never capture: '[)' means the lower bound belongs to the range and the upper bound does not, so a shift ending at 14:00 and one starting at 14:00 do not overlap.

Exclusion Constraints

A unique constraint says no two rows may share a value under equality. An exclusion constraint generalizes that to any operator a GiST index can evaluate: no two rows may satisfy this operator pairwise. Combine equality on the courier with overlap on the shift and double-booking stops being a rule the application enforces.

The constraint that makes a double booking impossible

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE courier_shifts
  ADD CONSTRAINT courier_shifts_no_double_booking
      EXCLUDE USING gist (courier_id WITH =, shift WITH &&);

btree_gist is needed because GiST has no built-in operator class for plain equality on a bigint; the extension supplies one so the two conditions can live in the same index. What matters is where the check now runs. An application-level test has a window between reading and writing, and a second session can slip through it; a constraint backed by an index is evaluated as part of the insert, so the second writer gets a constraint violation and no amount of concurrency produces an overlapping pair.

Where the double-booking check runs

In the dispatch servicea rule somebody has to remember

Read, decide, write — with a window in between that a second session can slip through. The comparison also has to be written correctly, and Cartwheel's uses < where it needs <=.

In an EXCLUDE constraintcourier_id WITH =, shift WITH &&

Evaluated as part of the insert, backed by a GiST index: the second writer gets a constraint violation, and no amount of concurrency produces an overlapping pair.

Multiranges

Since 14, every range type has a matching multirange type — tstzmultirange, datemultirange, int4multirange and the rest — holding an ordered list of non-contiguous, non-empty ranges as one value. "The hours this courier is booked this week" is naturally a multirange, and before 14 that answer had to be assembled in application code and then compared there too.

A set of disjoint periods as a single value

SELECT '{["2026-08-10 06:00+00","2026-08-10 14:00+00"),
      ["2026-08-12 06:00+00","2026-08-12 14:00+00")}'::tstzmultirange
       @> tstzrange('2026-08-12 07:00+00', '2026-08-12 09:00+00');
 t

One containment test answers "is this delivery window entirely inside hours the courier is working", across a week of separate shifts, without a loop and without a temporary table. The operators are the ones ranges already use: &&, @>, -|-.

Enums

An enum is a named type with an ordered list of labels. Each value occupies four bytes on disk, labels are case sensitive and limited to 63 bytes in a standard build, and comparison follows declaration order rather than alphabetical order, so a status column sorts through its own lifecycle for free.

Declaring a status type, and adding to it later

CREATE TYPE order_status AS ENUM
  ('pending', 'picking', 'dispatched', 'delivered', 'cancelled');

ALTER TYPE order_status ADD VALUE 'refunded' AFTER 'cancelled';

Since 12 that ADD VALUE may run inside a transaction block, with one restriction that catches people out: the new value cannot be used until the transaction commits, so a migration that adds a label and then inserts a row carrying it in the same transaction fails. Renaming a label is supported. Removing one is not supported at all, and neither is reordering, short of dropping and recreating the type with every dependent view and function. Enums therefore fit stable, code-owned sets, and fit badly where a product manager invents a new order state every quarter. Cartwheel keeps orders.status as text, and Chapter 3 puts a lookup table with a foreign key behind it.

Domains

A domain is a base type with constraints attached and a name of its own, so a rule that would otherwise be copied onto every table that stores that kind of value gets exactly one definition.

One rule, one definition, every column that uses it

CREATE DOMAIN email AS text
  CHECK (VALUE ~ '^[^@[:space:]]+@[^@[:space:]]+$');

ALTER TABLE customers ALTER COLUMN email TYPE email;

Domain constraints are checked whenever a value is converted to the domain type, which covers inserts, updates and explicit casts. There is one caveat: a NOT NULL inside a domain does not guarantee that a value of that domain always reads as non-null. On the nullable side of an outer join, or from an empty scalar subquery, a domain-typed expression comes back null and no constraint check applies to it. A column-level NOT NULL still does.

Array vs junction table

An array column — the set lives inline: one row, no join, GIN-indexable containment, and no referential integrity whatsoever. Right for tags and labels that are read with the row and have no attributes of their own.

A junction table — one row per pair, with foreign keys, per-pair columns and ordinary joins. Right for anything the business has rules about. order_items is a table for that reason: a line item carries a quantity and a unit price.

The deciding question — does an element need a column of its own, now or plausibly next year? If yes, it is a table. If a tag will never be more than a word, the array saves a join on every product read.

Common Mistakes

Replacing a junction table with an array of foreign ids — there is no foreign key to enforce, no room for a quantity or a price, and every aggregate goes through unnest() with an estimate the planner cannot trust.
Keeping shift_start and shift_end columns and hand-writing the overlap test — the version with < where <= belonged passes every test and double-books a courier at exactly the shift boundary.
Enforcing "no overlapping periods" in application code when an EXCLUDE constraint would do it — the application check has a window between read and write that a second session walks straight through.
Choosing an enum for a value set the business owns — you can add a label and rename one, you can never remove one, and reordering means dropping the type along with every view that depends on it.
Adding an enum value and inserting a row that uses it in the same transaction — permitted to add since 12, still not usable until that transaction has committed.
Treating a domain's NOT NULL as a guarantee that the value is always present — on the nullable side of an outer join the column reads as null and no domain check runs on it.

Best Practices

Model any period as a range type with explicit bound inclusivity, so the boundary case is stated in the data instead of argued about in a code review.
Enforce non-overlap with EXCLUDE USING gist and btree_gist, and delete the application-side check once the constraint is in place rather than keeping both.
Use arrays for small, attribute-free sets read alongside the row, and switch to a junction table the moment an element needs a column of its own.
Prefer a lookup table with a foreign key when the value set belongs to the business, and reserve enums for stable sets the code owns end to end.
Split an enum migration into two transactions when a new label has to be used immediately: add the value, commit, then write the rows.
Define a domain for any rule repeated across tables, such as an email format or a non-negative amount, so the rule has one definition and one place to change.

Comparable toolsOracle VARRAY, nested tables, PERIOD FORSQL Server table types and temporal tablesMySQL ENUM and SET, no arrays or rangesPostGIS the same EXCLUDE mechanism on geometry

Knowledge Check

Why is an array of product ids on orders a poor replacement for the order_items table?

Array elements take no foreign key and no attributes of their own
Arrays cannot be indexed, so finding orders containing a product needs a scan
Arrays are capped at a small number of elements, which real baskets exceed
Arrays can only hold text values, so numeric product ids do not fit

What does EXCLUDE USING gist (courier_id WITH =, shift WITH &&) actually guarantee on the courier_shifts table?

That each courier has exactly one shift row at any point in the table
That no two rows for the same courier hold shifts that overlap in time
That two shifts for a courier must be separated by a gap of at least a minute
That overlapping rows are rejected whenever the table is next read or vacuumed

A migration runs ALTER TYPE order_status ADD VALUE 'refunded' and then inserts an order with that status, all in one transaction. What happens?

The ALTER TYPE fails, because enum values cannot be added inside a transaction
The column is silently promoted to text so the unknown label can be stored
The insert fails, because the new value is unusable until the transaction commits
Both succeed, since the restriction was lifted along with transactional ADD VALUE

Which characteristic of enums argues most strongly for a lookup table when the value set belongs to the business?

An enum value costs far more storage per row than a foreign key would
A label can never be removed, and the ordering cannot be changed afterwards
An enum column cannot be indexed, so status filters always scan the table
Enum values sort alphabetically rather than in the order they were declared

What does a domain give you that repeating the same CHECK constraint on nine tables does not?

Cheaper validation, since domain checks run once per statement rather than per row
One definition of the rule that every column using the domain inherits
A more compact on-disk representation than the underlying base type
A guarantee that a NOT NULL domain column can never read as null

You got correct