The essentials
Quick reference
One focused task per row. Jump to the related section for complete, working examples.
| Use | Syntax | Examples |
|---|---|---|
| Construct a typed array | ARRAY['read', 'write']::text[] | View examples |
| Read the first element | permissions[1] | View examples |
| Count all elements | cardinality(permissions) | View examples |
| Match any element | 'admin' = ANY (permissions) | View examples |
| Test array containment | permissions @> ARRAY['read', 'write']::text[] | View examples |
| Test array overlap | permissions && ARRAY['admin', 'owner']::text[] | View examples |
| Expand with positions | CROSS JOIN LATERAL unnest(t.tags)
WITH ORDINALITY AS tag(value, position) | View examples |
| Create a half-open range | tstzrange(starts_at, ends_at, '[)') | View examples |
| Test range containment | valid_during @> now() | View examples |
| Test range overlap | reserved_during && tstzrange($1, $2, '[)') | View examples |
| Prevent overlapping bookings | EXCLUDE
USING gist (room_id
WITH =, reserved_during
WITH &&
) | View examples |
| Construct a date multirange | datemultirange(daterange('2026-08-01', '2026-08-08', '[)'), daterange('2026-08-15', '2026-08-22', '[)')) | View examples |
| Index array operators | CREATE INDEX documents_tags_gin_idx
ON app.documents
USING gin (tags); | View examples |
| Index range operators | CREATE INDEX bookings_during_gist_idx
ON app.bookings
USING gist (reserved_during); | View examples |
Arrays represent an ordered collection inside one row; ranges represent a set between bounds; multiranges represent an ordered set of non-overlapping, non-adjacent ranges. They are valuable when those shapes are truly atomic to the domain. Prefer child tables for independently constrained entities, choose operators that match index support, and test null, empty, infinite, and boundary behavior explicitly.
Step by step
Detailed examples
Use arrays for values owned and replaced as one attribute
An array column can hold any built-in or user-defined element type and may be multidimensional, but a declared dimension is documentation rather than an enforced size. PostgreSQL arrays usually start at subscript 1, although stored lower bounds can differ. Keep entities requiring foreign keys, individual lifecycle, or per-element metadata in a related table instead.
CREATE TABLE app.api_tokens (
token_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
permissions text[] NOT NULL DEFAULT ARRAY[]::text[],
CONSTRAINT permissions_no_nulls CHECK (array_position(permissions, NULL) IS NULL)
);
INSERT INTO app.api_tokens (permissions)
VALUES (ARRAY['read', 'write']::text[]);Choose scalar comparison, containment, or overlap deliberately
value = ANY(array) compares one scalar with each element. @> tests containment and && tests overlap. Array containment is not bag containment: duplicates are not counted specially. Null arrays and null elements introduce SQL three-valued logic, so disallow or normalize null elements when boolean membership must be decisive.
SELECT token_id
FROM app.api_tokens
WHERE 'read' = ANY (permissions)
AND permissions @> ARRAY['read', 'write']::text[]
AND NOT (permissions && ARRAY['suspended']::text[]);Expand arrays in FROM and preserve order explicitly
unnest emits array elements as rows. Put set-returning functions in FROM with LATERAL so their cardinality is visible, and add WITH ORDINALITY when the source ordering matters. Multiple arrays passed to unnest are padded with nulls to the longest input, which can silently misalign malformed parallel arrays.
SELECT d.document_id, tag.value, tag.position
FROM app.documents AS d
CROSS JOIN LATERAL unnest(d.tags) WITH ORDINALITY AS tag(value, position)
ORDER BY d.document_id, tag.position;Encode interval boundaries as part of the domain
Range bounds may be inclusive or exclusive, infinite, or empty. Half-open [start,end) ranges compose naturally for adjacent time windows. Discrete range types such as daterange are canonicalized, so text output can differ from input while representing the same set. Use isempty, lower, upper, and bound-inclusivity functions instead of parsing range text.
WITH rules(valid_during) AS (
VALUES (tstzrange(
'2026-08-12 09:00:00+00',
'2026-08-12 17:00:00+00',
'[)'
))
)
SELECT lower(valid_during) AS starts_at,
upper(valid_during) AS ends_at,
lower_inc(valid_during) AS includes_start,
upper_inc(valid_during) AS includes_end,
isempty(valid_during) AS is_empty
FROM rules;Prevent conflicting intervals with exclusion constraints
An exclusion constraint rejects a pair of rows when every listed operator compares true. For a booking rule, equality on a scalar room identifier and overlap on a time range express the invariant atomically under concurrency. The btree_gist extension supplies GiST operator classes for common scalar types; evaluate extension policy before installing it.
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE app.bookings (
booking_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
room_id bigint NOT NULL,
reserved_during tstzrange NOT NULL,
CONSTRAINT booking_not_empty CHECK (NOT isempty(reserved_during)),
CONSTRAINT room_booking_no_overlap
EXCLUDE USING gist (room_id WITH =, reserved_during WITH &&)
);Represent discontinuous availability with multiranges
A multirange stores zero or more ordered ranges of one subtype and normalizes overlaps or adjacency. Union, intersection, difference, containment, and expansion with unnest operate on the set rather than application-formatted text. Multiranges are useful for schedules with gaps; separate rows remain better when each interval has independent attributes.
WITH calendars(available) AS (
VALUES (datemultirange(
daterange('2026-08-01', '2026-08-08', '[)'),
daterange('2026-08-15', '2026-08-22', '[)')
))
)
SELECT slot
FROM calendars
CROSS JOIN LATERAL unnest(available) AS slot
ORDER BY lower(slot);Match GIN and GiST indexes to the operators in real queries
GIN is a common choice for array @>, <@, and && operators. GiST and SP-GiST support range operations, while GiST also underpins exclusion constraints. An index does not make every expression equivalent: parameter types, casts, operators, data distribution, and selectivity determine use. Verify representative predicates with EXPLAIN rather than indexing by type alone.
CREATE INDEX documents_tags_gin_idx
ON app.documents USING gin (tags);
CREATE INDEX bookings_during_gist_idx
ON app.bookings USING gist (reserved_during);
EXPLAIN (COSTS, VERBOSE)
SELECT booking_id
FROM app.bookings
WHERE reserved_during && tstzrange($1, $2, '[)');Sources and further reading
References
Authoritative documentation used to verify and expand this cheat sheet.
- PostgreSQL Global Development GroupPostgreSQL: Arrayspostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: Array Functions and Operatorspostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: Range Typespostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: Range and Multirange Functions and Operatorspostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: Constraintspostgresql.org
Help us improve
Found a typo or missing example?
Tell us what would make this cheat sheet clearer, more complete, or more useful.