RANGE_INTERSECT

Return the overlapping portion of two ranges, or an empty range if they do not overlap.

Category: miscReturns: RANGEDialect: PostgreSql

Syntax

RANGE_INTERSECT(range1, range2)

Description

## Overview Computes the intersection of two ranges. The result is the portion contained by both inputs; if the inputs do not overlap, the result is an empty range. Use this function to find overlapping time windows, numeric ranges, or event intervals. ## Behavior - Returns NULL if either argument is NULL. - Both arguments must have the same element type. - Returns a range value with inclusive or exclusive bounds that match the more restrictive input bounds. - Empty ranges are the identity: intersecting with an empty range returns empty. - Deterministic and side effect free. ## Compatibility - PG-compat alias for the `*` operator on range types.

Parameters

Name	Type	Description
`range1`		Specifies the first range. Must be of the same type as range2.
`range2`		Specifies the second range. Must be of the same type as range1.

Examples

-- Overlapping integer ranges
SELECT RANGE_INTERSECT('[1,10]', '[5,15]');  -- '[5,10]'

-- Non-overlapping ranges return an empty range
SELECT RANGE_INTERSECT('[1,5]', '[10,20]');  -- 'empty'

-- Identical ranges
SELECT RANGE_INTERSECT('[1,10]', '[1,10]');  -- '[1,10]'

-- Adjacent but non-overlapping ranges
SELECT RANGE_INTERSECT('[1,5]', '[5,10]');  -- '[5,5]' (single-point overlap)

-- Find overlapping reservation windows
SELECT reservation_id, RANGE_INTERSECT(window, requested) AS overlap
FROM booking.catalog.reservations;

Pitfalls

Both ranges must have the same element type. Mixing INT range and BIGINT range raises a type error; cast explicitly.
Single-point overlaps (`[5,5]`) are valid ranges, not empty. Downstream logic that treats a single point as 'no overlap' must check the width explicitly.
The bounds of the result inherit the inclusivity of the more restrictive side; a `[1,10)` intersected with `[5,15]` returns `[5,10)`, which may surprise code that expects symmetric inclusion.
NULL bounds in range arguments are treated as infinite open bounds; if you want NULL to mean 'no value', filter those rows out before calling.