Use when storing list-valued columns, modelling time/number intervals, or preventing overlapping bookings with exclusion constraints. Prevents seqscan on range-overlap queries from missing GiST index, CSV-in-text instead of native array, and off-by-one errors from inclusive vs exclusive range bounds. Covers array literals + operators (= ANY, @>, &&, ||), unnest WITH ORDINALITY, array_agg, range types (int4range, tstzrange, daterange), range operators (@>, &&, -|-), multirange (v14+), GiST exclusion constraints for non-overlap. Keywords: array, ARRAY, ANY, unnest, array_agg, range type, int4range, tstzrange, daterange, multirange, range overlap, exclusion constraint, GiST, EXCLUDE USING gist, overlapping bookings, how to store a list, prevent double booking, range query is slow
Use when storing list-valued columns, modelling time/number intervals, or preventing overlapping bookings with exclusion constraints. Prevents seqscan on range-overlap queries from missing GiST index, CSV-in-text instead of native array, and off-by-one errors from inclusive vs exclusive range bounds. Covers array literals + operators (= ANY, @>, &&, ||), unnest WITH ORDINALITY, array_agg, range types (int4range, tstzrange, daterange), range operators (@>, &&, -|-), multirange (v14+), GiST exclusion constraints for non-overlap. Keywords: array, ARRAY, ANY, unnest, array_agg, range type, int4range, tstzrange, daterange, multirange, range overlap, exclusion constraint, GiST, EXCLUDE USING gist, overlapping bookings, how to store a list, prevent double booking, range query is slow
license
MIT
compatibility
Designed for Claude Code. Requires PostgreSQL 15, 16, or 17.
metadata
{"author":"OpenAEC-Foundation","version":"1.0"}
postgres-syntax-arrays-ranges
Quick Reference :
PostgreSQL has two native multi-value types that replace ad-hoc string hacks.
Arrays store an ordered list inside one column (int[], text[]). Range
types store an interval ([lower, upper)) as a single value with built-in
overlap and containment logic.
Two rules drive every decision here :
ALWAYS use a native array instead of a comma-separated string in a text
column. CSV-in-text cannot be indexed, validated, or searched element-wise.
ALWAYS build a GiST index (or an EXCLUDE USING gist constraint) before
running range-overlap (&&) queries at scale. Without it the planner does a
sequential scan on every row.
Need to store many values in one row? -> array column + GIN index
Need to model a start..end interval? -> range type ([) bounds)
Need to forbid two rows overlapping in time? -> EXCLUDE USING gist (... WITH &&)
Need a union of disjoint intervals? -> multirange + range_agg
Array subscripts are 1-based. Range bounds default to [) : lower
inclusive, upper exclusive. Both defaults are load-bearing : ignoring them
produces silent off-by-one bugs.
When To Use This Skill :
ALWAYS use this skill when :
A column must hold a list of values (tags, role ids, scores per quarter)
Modelling a time, date, or numeric interval (booking window, price band, validity period)
Preventing overlapping reservations of a shared resource (room, vehicle, seat)
A range-overlap or array-containment query is slow and you suspect a missing index
Merging or aggregating many intervals into their union
NEVER use this skill for :
JSON document storage or key-value attributes : use postgres-syntax-jsonb
Generating a row series from a single range : use generate_series (set-returning function)
Spatial geometry intervals : use PostGIS range/geometry types
Decision Trees :
Array column vs separate child table :
Will you filter, join, or aggregate on the individual values?
├─ YES, and values reference another entity -> child table + FK (NOT an array)
├─ YES, simple membership / containment only -> array column + GIN index
└─ NO, values are read/written as a whole set -> array column (no index needed)
Do values need their own attributes (qty, added_at)?
└─ YES -> child table. An array stores bare scalars only.
Range type vs two scalar columns :
Do you store a start and an end of the same subtype?
├─ NO -> two ordinary columns
└─ YES -> Do you ever query overlap, containment, or adjacency?
├─ NO -> two columns is acceptable
└─ YES -> range type. Overlap (&&) on two columns needs
(start_a < end_b AND start_b < end_a) by hand;
a range type makes it one operator + one GiST index.
Which index for a multi-value column :
Array column, queried with @> <@ && or = ANY -> GIN index
Range column, queried with @> <@ && -|- -> GiST index
Range column, exact equality only -> btree (default) is enough
EXCLUDE constraint on a range -> GiST (the constraint creates it)
Exclusion constraint scoping :
Forbid ANY two rows overlapping?
└─ EXCLUDE USING gist (during WITH &&)
Forbid overlap only within the same group (same room, same vehicle)?
└─ CREATE EXTENSION btree_gist;
EXCLUDE USING gist (room WITH =, during WITH &&)
(the = operator needs btree_gist; && needs core GiST)
Patterns :
Pattern 1 : Native array column instead of CSV text
ALWAYS : declare the column with the element type plus [].
NEVER : store '1,2,3' in a text column and split it at query time.
CREATE TABLE article (
id bigint GENERATED ALWAYS ASIDENTITYPRIMARY KEY,
title text NOT NULL,
tags text[] NOT NULLDEFAULT'{}'
);
INSERT INTO article (title, tags)
VALUES ('Indexing 101', ARRAY['postgres', 'index', 'performance']);
WHY : a text[] column is type-checked per element, supports @> / && /
= ANY, and is GIN-indexable. A CSV string is opaque : every search becomes a
full-table LIKE '%tag%' scan that also matches substrings of other tags.
Pattern 2 : Membership test with = ANY and the GIN-indexed @>
ALWAYS : use @> (contains) for indexed membership filters.
NEVER : assume = ANY(array) uses a GIN index. It does not.
CREATE INDEX article_tags_gin ON article USING GIN (tags);
-- Index-eligible : "rows whose tags contain 'postgres'"SELECT id, title FROM article WHERE tags @>ARRAY['postgres'];
-- Overlap : "rows sharing ANY tag with this set"SELECT id FROM article WHERE tags &&ARRAY['index', 'sql'];
-- = ANY scans the array per row; fine for a small literal list,-- NOT index-accelerated against a column array.SELECT*FROM article WHERE'postgres'=ANY (tags);
WHY : the array_ops GIN opclass indexes @>, <@, and &&. = ANY is a
row-by-row scalar comparison, so it never consults the GIN index on tags.
Rewrite membership filters as tags @> ARRAY[value] to get the index.
Pattern 3 : Expand arrays to rows with unnest and WITH ORDINALITY
ALWAYS : add WITH ORDINALITY when the element position matters.
NEVER : rely on unnest output order without ORDINALITY if you then need the index.
-- One row per tag, preserving 1-based positionSELECT a.id, t.tag, t.pos
FROM article a,
LATERALunnest(a.tags) WITH ORDINALITY AS t(tag, pos);
-- Inverse of array_agg : rebuild an array from rowsSELECTarray_agg(tag ORDERBY pos) AS tags
FROM (SELECT'x'AS tag, 1AS pos) s
GROUPBY ();
WHY : unnest(arr) WITH ORDINALITY AS t(value, n) adds a bigint column with
the element's ordinal position. array_agg(expr ORDER BY ...) is the reverse :
it collapses rows back into an ordered array. Pair them to transform array data
through relational operators.
Pattern 4 : Range type with explicit [) bounds
ALWAYS : keep the default [) form : lower inclusive, upper exclusive.
NEVER : mix [] and [) across rows of the same column. Inconsistent bounds
break overlap math.
CREATE TABLE price_band (
sku text NOT NULL,
valid daterange NOT NULL,
unit_price numeric(10,2) NOT NULL
);
-- Two-arg constructor always yields [) ; 3-arg form sets bounds explicitlyINSERT INTO price_band VALUES
('A-1', daterange('2026-01-01', '2026-04-01'), 9.99), -- Jan..Mar
('A-1', daterange('2026-04-01', '2026-07-01'), 8.99); -- Apr..Jun-- No off-by-one : Apr 1 belongs to the second band, not bothSELECT unit_price FROM price_band
WHERE sku ='A-1'AND valid @>DATE'2026-04-01';
WHY : with [) bounds adjacent ranges ([Jan,Apr) and [Apr,Jul)) touch
without overlapping, so a boundary date lands in exactly one row. [] on both
would make April 1 match both bands and trigger any exclusion constraint.
Pattern 5 : GiST index for range-overlap queries
ALWAYS : create a GiST index before filtering a range column with && / @>.
NEVER : leave a range-overlap query on a btree-only column : btree cannot
answer &&, so the planner falls back to a sequential scan.
CREATE INDEX price_band_valid_gist ON price_band USING GIST (valid);
-- Index-eligible overlap querySELECT*FROM price_band
WHERE valid && daterange('2026-03-15', '2026-05-15');
WHY : range overlap is not a btree-orderable predicate. Only GiST (and SP-GiST)
expose &&, @>, <@, -|- to the planner. Without the GiST index, a
WHERE valid && ... filter reads every heap row.
Pattern 6 : EXCLUDE USING gist to prevent overlapping bookings
ALWAYS : enforce non-overlap with an exclusion constraint, not application code.
NEVER : check for conflicts with a SELECT then INSERT : that race window
lets two concurrent transactions both pass the check.
CREATE EXTENSION IF NOTEXISTS btree_gist;
CREATE TABLE room_reservation (
id bigint GENERATED ALWAYS ASIDENTITYPRIMARY KEY,
room text NOT NULL,
during tsrange NOT NULL,
EXCLUDE USING gist (room WITH=, during WITH&&)
);
INSERT INTO room_reservation (room, during)
VALUES ('123A', '[2026-05-20 14:00, 2026-05-20 15:00)');
-- Same room, overlapping window -> rejected with SQLSTATE 23P01INSERT INTO room_reservation (room, during)
VALUES ('123A', '[2026-05-20 14:30, 2026-05-20 15:30)');
WHY : the database enforces "no two rows share a room AND overlap in time"
atomically under concurrency. room WITH = needs the btree_gist extension
(GiST has no built-in equality opclass for text); during WITH && uses the
core range GiST opclass. The constraint also builds the GiST index for free.
Pattern 7 : Merge intervals with multirange and range_agg
ALWAYS : use range_agg to collapse many ranges into their union.
NEVER : hand-roll interval merging with sorted loops in SQL.
-- range_agg returns a multirange : the union of all input rangesSELECT range_agg(during) AS booked
FROM room_reservation
WHERE room ='123A';
-- -> {[2026-05-20 14:00,2026-05-20 15:00)} (disjoint parts stay separate)-- A multirange literal is a brace-wrapped set of rangesSELECT'{[1,5), [10,15)}'::int4multirange @>3; -- tSELECT'{[1,5), [10,15)}'::int4multirange @>7; -- f
WHY : multirange types (int4multirange, tstzmultirange, ...) represent a
union of disjoint ranges as one value. range_agg is the aggregate that builds
one. Multirange and range_agg exist since v14, so they are available in every
supported version (15, 16, 17).
Anti-Patterns :
(Short list, full detail in references/anti-patterns.md)
CSV string in a text column instead of a native array : no index, no type check
WHERE col && range on a btree-only range column : seqscan, SQLSTATE not raised but query is slow
[] inclusive upper bound on adjacent ranges : off-by-one double-match
= ANY(column_array) expecting GIN acceleration : never index-eligible, rewrite as @>
Application-side conflict check before INSERT : race condition, use EXCLUDE USING gist
Missing btree_gist for an EXCLUDE mixing = and && : SQLSTATE 42704 (undefined object)
Out-of-bounds array subscript treated as an error : it returns NULL, not SQLSTATE 2202E
Reference Links :
references/methods.md : Full operator and function tables for arrays, ranges, multiranges
