↔

PostgreSQL Range Type Functions

Complete reference for PostgreSQL range type functions covering range construction, containment testing, overlap detection, and boundary extraction. Covers int4range, numrange, tsrange, tstzrange, daterange, and custom range types. Updated for PostgreSQL 16.

10 functions

Jump to function

lower upper lower_inc upper_inc lower_inf upper_inf isempty range_merge range_agg range_intersect_agg

What are PostgreSQL Range Type Functions?

PostgreSQL range types represent a contiguous range of values of some element type, such as a range of dates or a range of integers. Range functions support containment (@>), overlap (&&), union, intersection, and difference operations. Range types are particularly useful for scheduling systems, booking applications, and time-series data where value ranges arise naturally.

lower

Range types (including `lower()`) introduced in PostgreSQL 9.2. Multirange variants require PostgreSQL 14+.→ same as range element type

Returns the lower bound of the range. Returns NULL if the range has no lower bound (unbounded).

DeveloperDBAData Eng

Signature

lower ( anyrange ) → anyelement

Parameters

Parameter	Type	Description
range	anyrange	Range value to extract lower bound from

Examples

sql

SELECT lower('[2025-01-01,2025-12-31]'::daterange);

→2025-01-01

sql

SELECT lower('[1,10)'::int4range);

→1

sql

SELECT lower('(,5)'::int4range);

→NULL (unbounded lower)

sql

SELECT id, lower(booking_period) AS check_in FROM room_bookings WHERE lower(booking_period) >= CURRENT_DATE ORDER BY 2;

→Upcoming check-in dates sorted chronologically

⚠Anti-Pattern— Extracting bounds from two-column start/end design instead of using a range type

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);

Use `lower_inf(range)` to test if the lower bound is infinite (unbounded) before calling `lower()`. `lower()` returns NULL for unbounded ranges, which can cause unexpected behavior in comparisons.

example

SELECT CASE WHEN lower_inf(period) THEN 'since forever' ELSE lower(period)::text END AS start FROM subscriptions;

→Human-readable range start

upper

Range types (including `upper()`) introduced in PostgreSQL 9.2. Multirange variants require PostgreSQL 14+.→ same as range element type

Returns the upper bound of the range. Returns NULL if the range has no upper bound (unbounded).

DeveloperDBAData Eng

Signature

upper ( anyrange ) → anyelement

Parameters

Parameter	Type	Description
range	anyrange	Range value

Examples

daterange normalizes to half-open [)

sql

SELECT upper('[2025-01-01,2025-12-31]'::daterange);

→2026-01-01

sql

SELECT upper('[1,10)'::int4range);

→10

sql

SELECT upper('[1,)'::int4range);

→NULL (unbounded upper)

sql

SELECT id, upper(shift) - INTERVAL '1 second' AS shift_end FROM employee_shifts WHERE upper_inf(shift) IS FALSE;

→Concrete end timestamp for each bounded shift

⚠Anti-Pattern— Comparing upper bound directly without accounting for exclusive boundary

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;

`daterange '[2025-01-01, 2025-12-31]'` stores as `[2025-01-01, 2026-01-01)` — the upper bound is exclusive and advanced by 1 day. This is canonical form. Use `upper(r) - 1` to get the inclusive end date for display.

example

SELECT lower(period) AS starts, upper(period) - 1 AS ends_inclusive FROM contracts;

→Inclusive start and end dates

lower_inc

Range types (including `lower_inc()`) introduced in PostgreSQL 9.2.→ boolean

Returns true if the lower bound of the range is inclusive (closed bracket [).

DeveloperDBAData Eng

Signature

lower_inc ( anyrange ) → boolean

Parameters

Parameter	Type	Description
range	anyrange	Range to test

Examples

sql

SELECT lower_inc('[1,10)'::int4range);

→true

sql

SELECT lower_inc('(1,10)'::int4range);

→false

sql

SELECT lower_inc('empty'::int4range);

→false

sql

SELECT period, lower_inc(period), lower(period) FROM price_tiers WHERE NOT lower_inc(period);

→Price tiers with exclusive lower bounds (unusual, worth auditing)

⚠Anti-Pattern— Assuming lower_inc is always true for custom range types

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.

When implementing range validation logic, check `lower_inc` and `upper_inc` to know whether the boundary value itself is included. Discrete ranges (int4range, daterange) are always normalized to [).

example

SELECT lower_inc(r), lower(r), upper(r), upper_inc(r) FROM ranges;

→Full boundary inspection of range values

upper_inc

Range types (including `upper_inc()`) introduced in PostgreSQL 9.2.→ boolean

Returns true if the upper bound of the range is inclusive (closed bracket ]).

DeveloperDBAData Eng

Signature

upper_inc ( anyrange ) → boolean

Parameters

Parameter	Type	Description
range	anyrange	Range to test

Examples

sql

SELECT upper_inc('[1,10]'::numrange);

→true

sql

SELECT upper_inc('[1,10)'::int4range);

→false

sql

SELECT upper_inc('[1,10]'::int4range);

→false — normalized to [1, 11)

sql

SELECT period FROM price_bands WHERE upper_inc(period) ORDER BY upper(period);

→Rows where the upper bound is inclusive (numrange/tsrange only)

⚠Anti-Pattern— Using upper_inc to determine whether a value is inside the range

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;

Integer and date ranges are always normalized to `[)` form regardless of input. `int4range '[1,10]'` is stored as `[1,11)`. So `upper_inc` is always false for int4range/daterange.

example

SELECT upper_inc('[1,10]'::int4range);

→false — normalized to [1, 11)

lower_inf

Range types (including `lower_inf()`) introduced in PostgreSQL 9.2.→ boolean

Returns true if the lower bound of the range is infinite (unbounded, written as '(,'...).

DeveloperDBAData Eng

Signature

lower_inf ( anyrange ) → boolean

Parameters

Parameter	Type	Description
range	anyrange	Range to test

Examples

sql

SELECT lower_inf('(,10)'::int4range);

→true

sql

SELECT lower_inf('[1,10)'::int4range);

→false

sql

SELECT count(*) FROM contracts WHERE lower_inf(valid_period);

→Number of contracts active since the beginning of time

sql

SELECT id, lower(valid_period) FROM contracts WHERE NOT lower_inf(valid_period) AND lower(valid_period) > CURRENT_DATE;

→Future-starting contracts

⚠Anti-Pattern— Calling lower() without guarding against unbounded lower

`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;

Store 'valid from the beginning of time' with `'(,2025-12-31)'::daterange`. Use `lower_inf()` to check for these records and handle the missing lower bound gracefully.

example

SELECT id, CASE WHEN lower_inf(valid) THEN '-∞' ELSE lower(valid)::text END AS valid_from FROM contracts;

→-∞ for open-ended contracts

upper_inf

Range types (including `upper_inf()`) introduced in PostgreSQL 9.2.→ boolean

Returns true if the upper bound of the range is infinite (unbounded, written as '...,)').

DeveloperDBAData Eng

Signature

upper_inf ( anyrange ) → boolean

Parameters

Parameter	Type	Description
range	anyrange	Range to test

Examples

sql

SELECT upper_inf('[1,)'::int4range);

→true

sql

SELECT upper_inf('[1,10)'::int4range);

→false

sql

SELECT * FROM subscriptions WHERE upper_inf(active_period);

→Currently active subscriptions (no end date)

sql

SELECT count(*) FILTER (WHERE upper_inf(tenure)) AS permanent_staff FROM employees;

→Count of employees with no scheduled end date

⚠Anti-Pattern— Using a nullable end-date column instead of an unbounded range

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);

Use `tstzrange(started_at, null)` (or `'[started,)'`) for currently active records. This is cleaner than a nullable `ended_at` column and enables range overlap checks with `&&` operator.

example

SELECT * FROM employee_assignments WHERE upper_inf(assignment_period) AND role_id = $1;

→Currently assigned employees in a role

isempty

Range types (including `isempty()`) introduced in PostgreSQL 9.2.→ boolean

Returns true if the range is empty (contains no points).

DeveloperDBAData Eng

Signature

isempty ( anyrange ) → boolean

Parameters

Parameter	Type	Description
range	anyrange	Range to test

Examples

sql

SELECT isempty('empty'::int4range);

→true

sql

SELECT isempty('[1,1)'::int4range);

→true

sql

SELECT isempty('[1,2)'::int4range);

→false

sql

SELECT a.id, b.id FROM slots a JOIN slots b ON NOT isempty(a.period * b.period) WHERE a.id < b.id;

→All pairs of overlapping time slots

⚠Anti-Pattern— Using isempty as the only guard instead of the && overlap operator

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;

Range intersection `*` can return an empty range when there's no overlap. Use `isempty(r1 * r2)` to check if two ranges share any points.

example

SELECT isempty('[1,5)'::int4range * '[6,10)'::int4range) AS no_overlap;

→true (ranges don't overlap)

range_merge

Range types and `range_merge()` introduced in PostgreSQL 9.2. For aggregate equivalents (`range_agg`, `range_intersect_agg`) you need PostgreSQL 14+.→ anyrange

Returns the smallest range that includes both given ranges. The ranges need not overlap or even be adjacent.

DeveloperDBAData Eng

Signature

range_merge ( anyrange, anyrange ) → anyrange

Parameters

Parameter	Type	Description
range1	anyrange	First range
range2	anyrange	Second range

Examples

sql

SELECT range_merge('[1,5)'::int4range, '[8,10)'::int4range);

→[1,10)

sql

SELECT range_merge('[2025-01-01,2025-03-31)'::daterange, '[2025-06-01,2025-12-31)'::daterange);

→[2025-01-01,2025-12-31)

sql

SELECT range_merge(tstzrange('2025-01-01','2025-04-01'), tstzrange('2025-03-15','2025-07-01')) AS merged;

→["2025-01-01 00:00:00+00","2025-07-01 00:00:00+00")

sql

SELECT project_id, range_merge(min(work_period), max(work_period)) AS total_span FROM work_packages GROUP BY project_id;

→Bounding date range from first start to last end per project

⚠Anti-Pattern— Using range_merge to check overlap or union instead of dedicated operators

`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+

Use `range_agg` to union a set of ranges. Use `range_merge` to find the bounding box of two specific ranges. For exact coverage (with gaps), use multiranges (PG14+).

example

SELECT range_merge(min_range, max_range) AS bounding_range FROM (SELECT min(period) AS min_range, max(period) AS max_range FROM schedules) t;

→Range from earliest start to latest end

range_agg

Requires PostgreSQL 14+. Multirange types were introduced in PostgreSQL 14 alongside `range_agg` and `range_intersect_agg`.→ anymultirange

Aggregate function that computes the union of all non-null input ranges, returning a multirange that covers exactly the same points as the union of the inputs (preserving gaps).

DeveloperDBAData Eng

Signature

range_agg ( anyrange ) → anymultirange

Parameters

Parameter	Type	Description
range_column	anyrange	Range values to aggregate

Examples

sql

SELECT range_agg(period) FROM leaves WHERE employee_id = 42;

→{[2025-01-06,2025-01-11),[2025-03-17,2025-03-22)} — disjoint leave windows preserved

sql

SELECT room_id, range_agg(booking_period) AS occupied FROM bookings GROUP BY room_id;

→Multirange of all occupied windows per room

sql

SELECT range_agg(shift) FROM employee_shifts WHERE department = 'ops' AND shift && tstzrange(NOW(), NOW() + INTERVAL '24 hours');

→Union of all ops shifts in the next 24 hours

sql

SELECT employee_id, range_agg(project_period) AS committed_windows FROM project_assignments GROUP BY employee_id HAVING range_agg(project_period) @> tstzrange(NOW(), NOW() + INTERVAL '7 days');

→Employees whose combined project coverage spans the next 7 days

⚠Anti-Pattern— Using range_merge aggregate pattern instead of range_agg

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;

Unlike `range_merge`, `range_agg` returns a multirange that shows exactly which points are covered, including gaps. Subtract from a bounding range to find uncovered windows: `range_merge(period)::int4multirange - range_agg(period)` gives you the gaps.

example

SELECT range_merge(period)::datemultirange - range_agg(period) AS uncovered FROM project_timeline;

→Multirange of uncovered gaps within the project span

range_intersect_agg

Requires PostgreSQL 14+. Multirange types and both range aggregate functions (`range_agg`, `range_intersect_agg`) were introduced in PostgreSQL 14.→ anyrange or anymultirange

Aggregate function that computes the intersection of all non-null input ranges (or multiranges). Returns the range of points that appear in every input range.

DeveloperDBAData Eng

Signatures

range_intersect_agg ( anyrange ) → anyrange

range_intersect_agg ( anymultirange ) → anymultirange

Parameters

Parameter	Type	Description
range_column	anyrange or anymultirange	Range values to intersect

Examples

sql

SELECT range_intersect_agg(availability) FROM employee_availability WHERE team_id = 7;

→Window when every team member is available

sql

SELECT range_intersect_agg(booking_window) FROM conference_room_options WHERE location = 'HQ';

→Overlapping availability across all HQ rooms

sql

SELECT project_id, range_intersect_agg(sprint_period) AS common_window FROM sprint_schedules GROUP BY project_id HAVING NOT isempty(range_intersect_agg(sprint_period));

→Projects where all sprints share a common date window

sql

SELECT isempty(range_intersect_agg(slot)) AS no_common_slot FROM proposed_meeting_times WHERE meeting_id = $1;

→true if participants have no overlapping availability

⚠Anti-Pattern— Iterative application of the * operator instead of range_intersect_agg

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;

Collect each participant's available ranges into a table and run `range_intersect_agg(availability)` to find when everyone is free simultaneously. Combine with `isempty()` to detect scheduling conflicts.

example

SELECT range_intersect_agg(free_window) AS everyone_free FROM participant_schedules WHERE meeting_id = $1;

→Timestamp range when all participants are simultaneously available

PostgreSQL Range Type Functions

Jump to function

What are PostgreSQL Range Type Functions?

lower

Signature

Parameters

Examples

upper

Signature

Parameters

Examples

lower_inc

Signature

Parameters

Examples

upper_inc

Signature

Parameters

Examples

lower_inf

Signature

Parameters

Examples

upper_inf

Signature

Parameters

Examples

isempty

Signature

Parameters

Examples

range_merge

Signature

Parameters

Examples

range_agg

Signature

Parameters

Examples

range_intersect_agg

Signatures

Parameters

Examples

Related PostgreSQL Categories