PostgreSQL Range Functions and Operators

Below table shows the specialised operators and functions available for range and multi-range calculation

Range Operators

anyrange @> anyrange → boolean
Does the first range contain the second?
select int4range(2,4) @> int4range(2,3); → True
anyrange @> anyelement → boolean
Does the range contain the element?
select ‘[2011-01-01,2011-03-01)’::tsrange @> ‘2011-01-10’::timestamp; → True
anyrange <@ anyrange → boolean
Is the first range contained by the second?
select int4range(2,4) <@ int4range(1,7); → True
anyelement <@ anyrange → boolean
Is the element contained in the range?
select 42 <@ int4range(1,7); → False
anyrange && anyrange → boolean
Do the ranges overlap, that is, have any elements in common?
select int8range(3,7) && int8range(4,12);→ True
anyrange << anyrange → boolean
Is the first range strictly left of the second?
select int8range(1,10) << int8range(100,110); → True
anyrange >> anyrange → boolean
Is the first range strictly right of the second?
select int8range(50,60) >> int8range(20,30); → True
anyrange &< anyrange → boolean
Does the first range not extend to the right of the second?
select int8range(1,20) &< int8range(18,20); → True
anyrange &> anyrange → boolean
Does the first range not extend to the left of the second?
select int8range(7,20) &> int8range(5,10); → True
anyrange -|- anyrange → boolean
Are the ranges adjacent?
select numrange(1.1,2.2) -|- numrange(2.2,3.3); → True
anyrange + anyrange → anyrange
Computes the union of the ranges. The ranges must overlap or be adjacent, so that the
union is a single range (but see range_merge()).
select numrange(5,15) + numrange(10,20); → [5,20)
anyrange * anyrange → anyrange
Computes the intersection of the ranges.
select int8range(5,15) * int8range(10,20); → [10,15)
anyrange – anyrange → anyrange
Computes the difference of the ranges. The second range must not be contained in the
first in such a way that the difference would not be a single range.
select int8range(5,15) – int8range(10,20); → [5,10)

Multi-Range Operators

anymultirange @> anymultirange → boolean
Does the first multirange contain the second?
select ‘{[2,4)}’::int4multirange @> ‘{[2,3)}’::int4multirange; → True
anymultirange @> anyrange → boolean
Does the multirange contain the range?
select ‘{[2,4)}’::int4multirange @> int4range(2,3); → True
anymultirange @> anyelement → boolean
Does the multirange contain the element?
select ‘{[2011-01-01,2011-03-01)}’::tsmultirange @> ‘2011-01-10’::timestamp;→ True
anyrange @> anymultirange → boolean
Does the range contain the multirange?
select ‘[2,4)’::int4range @> ‘{[2,3)}’::int4multirange;→ True
anymultirange <@ anymultirange → boolean
Is the first multirange contained by the second?
select ‘{[2,4)}’::int4multirange <@ ‘{[1,7)}’::int4multirange; → True
anymultirange <@ anyrange → boolean
Is the multirange contained by the range?
select ‘{[2,4)}’::int4multirange <@ int4range(1,7);→ True
anyrange <@ anymultirange → boolean
Is the range contained by the multirange?
select int4range(2,4) <@ ‘{[1,7)}’::int4multirange;→ True
anyelement <@ anymultirange → boolean
Is the element contained by the multirange?
select 4 <@ ‘{[1,7)}’::int4multirange;→ True
anymultirange && anymultirange → boolean
Do the multiranges overlap, that is, have any elements in common?
select ‘{[3,7)}’::int8multirange && ‘{[4,12)}’::int8multirange;→ True
anymultirange && anyrange → boolean
Does the multirange overlap the range?
select ‘{[3,7)}’::int8multirange && int8range(4,12); → True
anyrange && anymultirange → boolean
Does the range overlap the multirange?
select int8range(3,7) && ‘{[4,12)}’::int8multirange;→ True
anymultirange << anymultirange → boolean
Is the first multirange strictly left of the second?
select ‘{[1,10)}’::int8multirange << ‘{[100,110)}’::int8multirange; → True
anymultirange << anyrange → boolean
Is the multirange strictly left of the range?
select ‘{[1,10)}’::int8multirange << int8range(100,110); → True
anyrange << anymultirange → boolean
Is the range strictly left of the multirange?
select int8range(1,10) << ‘{[100,110)}’::int8multirange; → True
anymultirange >> anymultirange → boolean
Is the first multirange strictly right of the second?

select ‘{[50,60)}’::int8multirange >> ‘{[20,30)}’::int8multirange
; → True
anymultirange >> anyrange → boolean
Is the multirange strictly right of the range
select ‘{[50,60)}’::int8multirange >> int8range(20,30); → True
anyrange >> anymultirange → boolean
Is the range strictly right of the multirange?
select int8range(50,60) >> ‘{[20,30)}’::int8multirange; → True
anymultirange &< anymultirange → boolean
Does the first multirange not extend to the right of the second?
select ‘{[1,20)}’::int8multirange &< ‘{[18,20)}’::int8multirange;
True
anymultirange &< anyrange → boolean
Does the multirange not extend to the right of the range?
select ‘{[1,20)}’::int8multirange &< int8range(18,20); → True
anyrange &< anymultirange → boolean
Does the range not extend to the right of the multirange?
select int8range(1,20) &< ‘{[18,20)}’::int8multirange; → True
anymultirange &> anymultirange → boolean
Does the first multirange not extend to the left of the second?
select ‘{[7,20)}’::int8multirange &> ‘{[5,10)}’::int8multirange; → True
anymultirange &> anyrange → boolean
Does the multirange not extend to the left of the range?
select ‘{[7,20)}’::int8multirange &> int8range(5,10); → True
anyrange &> anymultirange → boolean
Does the range not extend to the left of the multirange?
select int8range(7,20) &> ‘{[5,10)}’::int8multirange; → True
anymultirange -|- anymultirange → boolean
Are the multiranges adjacent?
select ‘{[1.1,2.2)}’::nummultirange -|- ‘{[2.2,3.3)}’::nummultirange; → True
anymultirange -|- anyrange → boolean
Is the multirange adjacent to the range?
select ‘{[1.1,2.2)}’::nummultirange -|- numrange(2.2,3.3); → True
anyrange -|- anymultirange → boolean
Is the range adjacent to the multirange?
select numrange(1.1,2.2) -|- ‘{[2.2,3.3)}’::nummultirange; → True
anymultirange + anymultirange → anymultirange
Computes the union of the multiranges. The multiranges need not overlap or be adjacent.
select ‘{[5,10)}’::nummultirange + ‘{[15,20)}’::nummultirange ;
{[5,10), [15,20)}
anymultirange * anymultirange → anymultirange
Computes the intersection of the multiranges.
select ‘{[5,15)}’::int8multirange * ‘{[10,20)}’::int8multirange ;
{[10,15)}
anymultirange – anymultirange → anymultirange
Computes the difference of the multiranges.
select ‘{[5,20)}’::int8multirange – ‘{[10,15)}’::int8multirange;
{[5,10), [15,20)}

Range Functions

lower ( anyrange ) → anyelement
Extracts the lower bound of the range (NULL if the range is empty or the lower bound is
infinite).
select lower(numrange(1.1,2.2)); → 1.1
upper ( anyrange ) → anyelement
Extracts the upper bound of the range (NULL if the range is empty or the upper bound is
infinite).
select upper(numrange(1.1,2.2)); → 2.2
isempty ( anyrange ) → boolean
Is the range empty?
select isempty(numrange(1.1,2.2)); → False
lower_inc ( anyrange ) → boolean
Is the range’s lower bound inclusive?
select lower_inc(numrange(1.1,2.2)); → True
upper_inc ( anyrange ) → boolean
Is the range’s upper bound inclusive?
select upper_inc(numrange(1.1,2.2)); → False
lower_inf ( anyrange ) → boolean
Is the range’s lower bound infinite?
select lower_inf(‘(,)’::daterange); → True
upper_inf ( anyrange ) → boolean
Is the range’s upper bound infinite?
select upper_inf(‘(,)’::daterange); → True
range_merge ( anyrange, anyrange ) → anyrange
Computes the smallest range that includes both of the given ranges.
select range_merge(‘[1,2)’::int4range, ‘[3,4)’::int4range); → [1,4)

Multi-Range Functions

lower ( anymultirange ) → anyelement
Extracts the lower bound of the multirange (NULL if the multirange is empty or the lower
bound is infinite).
select lower(‘{[1.1,2.2)}’::nummultirange); → 1.1
upper ( anymultirange ) → anyelement
Extracts the upper bound of the multirange (NULL if the multirange is empty or the upper
bound is infinite).
select upper(‘{[1.1,2.2)}’::nummultirange); → 2.2
isempty ( anymultirange ) → boolean
Is the multirange empty?
select isempty(‘{[1.1,2.2)}’::nummultirange); → False
lower_inc ( anymultirange ) → boolean
Is the multirange’s lower bound inclusive?
select lower_inc(‘{[1.1,2.2)}’::nummultirange); → True
upper_inc ( anymultirange ) → boolean
Is the multirange’s upper bound inclusive?
select upper_inc(‘{[1.1,2.2)}’::nummultirange); → False
lower_inf ( anymultirange ) → boolean
Is the multirange’s lower bound infinite?
select lower_inf(‘{(,)}’::datemultirange); → True
upper_inf ( anymultirange ) → boolean
Is the multirange’s upper bound infinite?
select upper_inf(‘{(,)}’::datemultirange); → True
range_merge ( anymultirange ) → anyrange
Computes the smallest range that includes the entire multirange.
select range_merge(‘{[1,2), [3,4)}’::int4multirange); → [1,4)
multirange ( anyrange ) → anymultirange
Returns a multirange containing just the given range.
select multirange(‘[1,2)’::int4range); → {[1,2)}
unnest ( anymultirange ) → setof anyrange
Expands a multirange into a set of ranges. The ranges are read out in storage order (ascending).
select unnest(‘{[1,2), [3,4)}’::int4multirange);
[1,2)
[3,4)