Arrays, ranges and generate_series

conceptThe concept

Arrays: 'eco' = ANY(tags) tests membership, tags @> '{eco,sale}' containment, tags && '{eco,sale}' overlap. unnest(tags) turns an array into rows (WITH ORDINALITY adds the position) and array_agg goes back. Arrays are 1-based. GIN indexes support @>, && and =.

Ranges such as daterange, tstzrange and int4range have inclusive or exclusive bounds ('[2025-01-01,2025-02-01)'). Operators: && overlaps, @> contains, -|- adjacent, * intersection. Since 14, multiranges hold a set of non-overlapping ranges, and range_agg merges ranges.

An exclusion constraint generalises UNIQUE: EXCLUDE USING gist (during WITH &&) forbids any two rows whose ranges overlap. generate_series produces numbers or timestamps and is the standard way to fill missing days in a report.

whyWhy it matters

Overlap checks written by hand (a.start < b.end AND b.start < a.end) are easy to get wrong at the boundaries and impossible to enforce under concurrency without a constraint. Range types and exclusion constraints make the database guarantee it.

Use caseReal-world scenario

The warehouse schedules maintenance windows that must never overlap, and the sales report must show every day of the month, including days with zero orders.

Try itExamples

Run and edit these in the interactive lesson.

SELECT name, tags,
       'eco' = ANY (tags) AS is_eco,
       tags @> '{eco,sale}' AS eco_and_sale,
       tags && '{limited,new}' AS limited_or_new,
       cardinality(tags) AS n
FROM products
WHERE cardinality(tags) >= 2
ORDER BY id
LIMIT 6;

SELECT tag, count(*) AS products, round(avg(price), 2) AS avg_price
FROM products, unnest(tags) AS tag
GROUP BY tag
ORDER BY products DESC;

SELECT category, array_agg(DISTINCT t ORDER BY t) AS tags_used
FROM products, unnest(tags) t
GROUP BY category
ORDER BY category;

SELECT daterange('2025-01-01', '2025-02-01') AS january,
       daterange('2025-01-01', '2025-02-01') @> date '2025-01-31' AS has_jan_31,
       daterange('2025-01-01', '2025-02-01') && daterange('2025-01-31', '2025-03-01') AS overlaps,
       daterange('2025-01-01', '2025-02-01') -|- daterange('2025-02-01', '2025-03-01') AS adjacent,
       range_agg(r) AS merged
FROM (VALUES (int4range(1, 5)), (int4range(3, 8)), (int4range(10, 12))) v(r);

BEGIN;
CREATE TABLE maintenance (
  id      int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  reason  text NOT NULL,
  during  tstzrange NOT NULL,
  EXCLUDE USING gist (during WITH &&)
);
INSERT INTO maintenance (reason, during) VALUES ('disk swap', '[2025-11-01 22:00, 2025-11-02 02:00)');
INSERT INTO maintenance (reason, during) VALUES ('kernel patch', '[2025-11-02 01:00, 2025-11-02 03:00)');

SELECT d::date AS day, count(o.id) AS orders
FROM generate_series(timestamptz '2025-12-20', timestamptz '2025-12-31', interval '1 day') d
LEFT JOIN orders o ON o.created_at >= d AND o.created_at < d + interval '1 day'
GROUP BY d
ORDER BY d;

internalsUnder the hood

Arrays are stored inline (or TOASTed when large) and updating one element rewrites the whole array, so very large or frequently appended arrays become slow; model those as a child table. Exclusion constraints are enforced through a GiST index and check concurrent inserts just as UNIQUE does.

Avoid / preferDo this, not that

SELECT name FROM products WHERE tags::text LIKE '%sale%';

SELECT name FROM products WHERE tags @> ARRAY['sale'];

CREATE TABLE bookings (room int, starts timestamptz, ends timestamptz);

CREATE TABLE bookings (
  room int, during tstzrange,
  EXCLUDE USING gist (room WITH =, during WITH &&)
);

PitfallsCommon mistakes

• Writing tags = '{eco}' to test membership. That compares whole arrays; use 'eco' = ANY(tags) or tags @> '{eco}'.
• Storing unbounded, growing lists (all order ids of a customer) in an array. Use a child table.
• Using closed upper bounds for time ranges. Half-open [start, end) ranges tile perfectly with no overlaps or gaps.

LimitsWhen not to use it

Do not use an array of ids as a substitute for a junction table when the ids reference another table: arrays cannot have foreign keys, and joins through unnest are clumsy and slow at scale.

SecuritySecurity notes

Pass lists from the application as one array parameter (WHERE id = ANY($1)) instead of building IN (1, 2, 3) strings; it is injection-safe and keeps one prepared plan.

ChallengeTry it yourself

SELECT name, tags
FROM products
WHERE tags @> ARRAY['eco', 'sale']
ORDER BY name;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Arrays
• Range types