9.19. Range Functions and Operators
See Section 8.17 for an overview of range types.
Table 9.50 shows the operators available for range types.
Table 9.50. Range Operators
Operator  Description  Example  Result 

=

equal 
int4range(1,5) = '[1,4]'::int4range

t

<>

not equal 
numrange(1.1,2.2) <> numrange(1.1,2.3)

t

<

less than 
int4range(1,10) < int4range(2,3)

t

>

greater than 
int4range(1,10) > int4range(1,5)

t

<=

less than or equal 
numrange(1.1,2.2) <= numrange(1.1,2.2)

t

>=

greater than or equal 
numrange(1.1,2.2) >= numrange(1.1,2.0)

t

@>

contains range 
int4range(2,4) @> int4range(2,3)

t

@>

contains element 
'[20110101,20110301)'::tsrange @> '20110110'::timestamp

t

<@

range is contained by 
int4range(2,4) <@ int4range(1,7)

t

<@

element is contained by 
42 <@ int4range(1,7)

f

&&

overlap (have points in common) 
int8range(3,7) && int8range(4,12)

t

<<

strictly left of 
int8range(1,10) << int8range(100,110)

t

>>

strictly right of 
int8range(50,60) >> int8range(20,30)

t

&<

does not extend to the right of 
int8range(1,20) &< int8range(18,20)

t

&>

does not extend to the left of 
int8range(7,20) &> int8range(5,10)

t



is adjacent to 
numrange(1.1,2.2)  numrange(2.2,3.3)

t

+

union 
numrange(5,15) + numrange(10,20)

[5,20)

*

intersection 
int8range(5,15) * int8range(10,20)

[10,15)



difference 
int8range(5,15)  int8range(10,20)

[5,10)

The simple comparison operators
<
,
>
,
<=
, and
>=
compare the lower bounds first, and only if those
are equal, compare the upper bounds. These comparisons are not usually
very useful for ranges, but are provided to allow Btree indexes to be
constructed on ranges.
The leftof/rightof/adjacent operators always return false when an empty range is involved; that is, an empty range is not considered to be either before or after any other range.
The union and difference operators will fail if the resulting range would need to contain two disjoint subranges, as such a range cannot be represented.
Table 9.51 shows the functions available for use with range types.
Table 9.51. Range Functions
Function  Return Type  Description  Example  Result 


range's element type  lower bound of range 
lower(numrange(1.1,2.2))

1.1


range's element type  upper bound of range 
upper(numrange(1.1,2.2))

2.2


boolean

is the range empty? 
isempty(numrange(1.1,2.2))

false


boolean

is the lower bound inclusive? 
lower_inc(numrange(1.1,2.2))

true


boolean

is the upper bound inclusive? 
upper_inc(numrange(1.1,2.2))

false


boolean

is the lower bound infinite? 
lower_inf('(,)'::daterange)

true


boolean

is the upper bound infinite? 
upper_inf('(,)'::daterange)

true


anyrange

the smallest range which includes both of the given ranges 
range_merge('[1,2)'::int4range, '[3,4)'::int4range)

[1,4)

The
lower
and
upper
functions return null
if the range is empty or the requested bound is infinite.
The
lower_inc
,
upper_inc
,
lower_inf
, and
upper_inf
functions all return false for an empty range.