PostgreSQL Range Type Functions

Calling `isempty(r1 * r2)` computes a full intersection just to test overlap. The `&&` operator tests overlap directly without materialising the intersection result and is index-eligible with a GiST index. Use `&&` in WHERE clauses; reserve `isempty` for checking the result of an intersection you already needed.

✓ Instead: SELECT * FROM bookings WHERE room_id = $1 AND period && $new_period::daterange;

Storing a range as separate `start_date` and `end_date` columns forces you to write `WHERE start_date <= $ts AND end_date > $ts` everywhere. A range column with a GiST index handles overlap, containment, and adjacency in a single operator (`&&`, `@>`, `-|-`) and the index is used automatically.

✓ Instead: ALTER TABLE bookings ADD COLUMN period daterange GENERATED ALWAYS AS (daterange(start_date, end_date)) STORED; CREATE INDEX ON bookings USING gist(period);

Built-in discrete range types (int4range, daterange) always normalize to `[)`, so `lower_inc` is always true. But custom range types using continuous subtypes (numrange, tsrange) preserve whatever boundary form was inserted. Code that blindly assumes `lower_inc = true` will produce wrong results when a `(` lower bound was stored.

✓ Instead: Always test `lower_inc(r)` explicitly when writing generic range-handling functions that must work with continuous range types.

`lower()` returns NULL for unbounded ranges. Passing that NULL into arithmetic, string formatting, or an ORDER BY without a NULL guard produces silent wrong results or unexpected sort order. Always check `lower_inf(r)` first or use `COALESCE(lower(r), '-infinity'::date)` for date ranges.

✓ Instead: SELECT COALESCE(lower(valid_period), '-infinity'::date) AS effective_from FROM contracts ORDER BY 1;

Representing N discontiguous date ranges as N rows forces complex self-joins and aggregation for overlap queries. A single multirange column eliminates the join entirely.

✓ Instead: ALTER TABLE schedules ADD COLUMN busy_periods datemultirange; -- Then: WHERE NOT busy_periods && query_range

Storing multiple ranges as a `daterange[]` array loses the automatic merging, sorting, and operator support (`&&`, `@>`, `-`, `+`) that multirange provides. Array operations require manual iteration and do not benefit from GiST index support for overlap queries.

✓ Instead: -- Store as a multirange column: ALTER TABLE schedules ADD COLUMN available_windows datemultirange; CREATE INDEX ON schedules USING gist(available_windows); -- Query with overlap: SELECT * FROM schedules WHERE available_windows && '[2025-06-01,2025-06-07)'::datemultirange;

A common pre-PG14 pattern is `range_merge(min(period), max(period))` to get a single bounding range across all rows. This throws away gap information and can include times with no actual coverage. In PG14+ use `range_agg` to get exact coverage as a multirange, and only use `range_merge` when you explicitly want the bounding box.

✓ Instead: -- PG 14+: exact union preserving gaps SELECT range_agg(period) FROM schedules WHERE employee_id = $1;

Intersecting ranges one at a time with application-side loops or recursive CTEs is O(n) round-trips and fragile. `range_intersect_agg` performs the full intersection server-side in one pass. Similarly, chaining `r1 * r2 * r3` in SQL only works for a known fixed number of ranges.

✓ Instead: SELECT range_intersect_agg(availability) FROM team_windows WHERE team_id = $1 AND week = $2;

Joining a table to itself to find overlapping ranges is O(N²) and produces complex, fragile SQL.

✓ Instead: Use range_intersect_agg(available) GROUP BY meeting_id for O(N) intersection of all attendee windows.

`range_merge` spans the gap between two ranges regardless of whether they overlap — it is a bounding-box function, not a union. If you use it to test overlap you will get false positives for ranges that are disjoint but close together. Use `&&` to test overlap, `+` for the true union (which only works on overlapping/adjacent ranges and raises an error otherwise), or multiranges (PG14+) for exact non-contiguous unions.

✓ Instead: -- Test overlap: r1 && r2 -- True union of adjacent/overlapping ranges: r1 + r2 -- Non-contiguous union: r1::int4multirange + r2::int4multirange -- PG 14+

unnest() splits the multirange into rows — any subsequent per-entity aggregation requires re-grouping. If you only need overlap or containment tests, operate on the multirange directly.

✓ Instead: Use WHERE mr && query_range directly (no unnest needed) for overlap tests; unnest only when you need to inspect or transform individual constituent ranges.

For discrete ranges like `daterange`, the stored upper bound is always exclusive (`[)`). Comparing `upper(period) = '2025-12-31'` will silently miss rows because the stored value is `2026-01-01`. Always use `upper(period) - 1` for inclusive display or write the filter as a containment check: `period @> '2025-12-31'::date`.

✓ Instead: SELECT * FROM contracts WHERE period @> '2025-12-31'::date;

Reconstructing membership logic manually — `v < upper(r) OR (upper_inc(r) AND v = upper(r))` — is verbose and error-prone. The containment operator `r @> v` does exactly this in a single, index-eligible expression.

✓ Instead: SELECT * FROM schedules WHERE period @> $check_value::timestamptz;

A nullable `ended_at` column requires `WHERE ended_at IS NULL OR ended_at > NOW()` everywhere. An unbounded upper range `tstzrange(started_at, NULL)` lets you write `WHERE active_period @> NOW()` and index it with GiST, covering both active and historical records in one expression.

✓ Instead: ALTER TABLE subscriptions ADD COLUMN active_period tstzrange; UPDATE subscriptions SET active_period = tstzrange(started_at, ended_at); CREATE INDEX ON subscriptions USING gist(active_period);