Range functions and operators
AnalyticDB for PostgreSQL supports the range functions and operators of PostgreSQL. Use them to compare, filter, and inspect range values — for example, finding all reservations that overlap a given time window, or checking whether a price falls within a discount range.
For the full PostgreSQL specification, see Range Functions and Operators.
Range operators
Range operators work on range expressions and return a result of the indicated type.
| Operator | Signature | Description | Example | Result |
|---|---|---|---|---|
= | anyrange = anyrange → boolean | Checks whether two ranges are equal. | int4range(1,5) = '[1,4]'::int4range | t |
<> | anyrange <> anyrange → boolean | Checks whether two ranges are not equal. | numrange(1.1,2.2) <> numrange(1.1,2.3) | t |
< | anyrange < anyrange → boolean | Checks whether the left range is less than the right range. | int4range(1,10) < int4range(2,3) | t |
> | anyrange > anyrange → boolean | Checks whether the left range is greater than the right range. | int4range(1,10) > int4range(1,5) | t |
<= | anyrange <= anyrange → boolean | Checks whether the left range is less than or equal to the right range. | numrange(1.1,2.2) <= numrange(1.1,2.2) | t |
>= | anyrange >= anyrange → boolean | Checks whether the left range is greater than or equal to the right range. | numrange(1.1,2.2) >= numrange(1.1,2.0) | t |
@> | anyrange @> anyrange → boolean | Checks whether the left range contains the right range. | int4range(2,4) @> int4range(2,3) | t |
@> | anyrange @> anyelement → boolean | Checks whether the left range contains the element. | '[2011-01-01,2011-03-01)'::tsrange @> '2011-01-10'::timestamp | t |
<@ | anyrange <@ anyrange → boolean | Checks whether the left range falls within the right range. | int4range(2,4) <@ int4range(1,7) | t |
<@ | anyelement <@ anyrange → boolean | Checks whether the element falls within the right range. | 42 <@ int4range(1,7) | f |
&& | anyrange && anyrange → boolean | Checks whether two ranges overlap (share at least one point). | int8range(3,7) && int8range(4,12) | t |
<< | anyrange << anyrange → boolean | Checks whether the left range is strictly left of the right range with no overlap. | int8range(1,10) << int8range(100,110) | t |
>> | anyrange >> anyrange → boolean | Checks whether the left range is strictly right of the right range with no overlap. | int8range(50,60) >> int8range(20,30) | t |
&< | anyrange &< anyrange → boolean | Checks whether the left range does not extend to the right of the right range. | int8range(1,20) &< int8range(18,20) | t |
&> | anyrange &> anyrange → boolean | Checks whether the left range does not extend to the left of the right range. | int8range(7,20) &> int8range(5,10) | t |
-|- | anyrange -|- anyrange → boolean | Checks whether two ranges are adjacent (touch at exactly one endpoint with no gap). | numrange(1.1,2.2) -|- numrange(2.2,3.3) | t |
+ | anyrange + anyrange → anyrange | Returns the union of two ranges. | numrange(5,15) + numrange(10,20) | [5,20) |
* | anyrange * anyrange → anyrange | Returns the intersection of two ranges. | int8range(5,15) * int8range(10,20) | [10,15) |
- | anyrange - anyrange → anyrange | Returns the difference of two ranges (left minus right). | int8range(5,15) - int8range(10,20) | [5,10) |
Usage notes
Comparison operators (`<`, `>`, `<=`, `>=`) compare lower bounds first, then upper bounds if the lower bounds are equal. These comparisons support B-tree index construction on range columns but are generally not meaningful for most business queries.
Positional operators (`<<`, `>>`, `-|-`) always return false when either operand is an empty range. An empty range is not considered to be before, after, or adjacent to any range.
Union (`+`) and difference (`-`) fail if the result would contain two disjoint sub-ranges, because a single range type cannot represent a discontinuous set. For example, int8range(1,5) + int8range(10,15) raises an error.
Range functions
Range functions return bounds, check properties, or merge ranges.
| Function | Return type | Description | Example | Result |
|---|---|---|---|---|
lower(anyrange) | Element data type | Returns the lower bound of the range. | lower(numrange(1.1,2.2)) | 1.1 |
upper(anyrange) | Element data type | Returns the upper bound of the range. | upper(numrange(1.1,2.2)) | 2.2 |
isempty(anyrange) | boolean | Checks whether a range is empty. | isempty(numrange(1.1,2.2)) | false |
lower_inc(anyrange) | boolean | Checks whether the lower bound is inclusive. | lower_inc(numrange(1.1,2.2)) | true |
upper_inc(anyrange) | boolean | Checks whether the upper bound is inclusive. | upper_inc(numrange(1.1,2.2)) | false |
lower_inf(anyrange) | boolean | Checks whether the lower bound is infinite. | lower_inf('(,)'::daterange) | true |
upper_inf(anyrange) | boolean | Checks whether the upper bound is infinite. | upper_inf('(,)'::daterange) | true |
range_merge(anyrange, anyrange) | anyrange | Returns the smallest range that includes both input ranges. | range_merge('[1,2)'::int4range, '[3,4)'::int4range) | [1,4) |