PostgreSQL Range Functions and Operators

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

PostgreSQL Range Functions and Operators

Introduction

Here the operators available for range types.

Range Operators

Operator Description Example Result
= equal SELECT int4range(1,5) = '[1,4]'::int4range;
 ?column?
----------
 t
(1 row)
<> not equal SELECT numrange(3.5,4.3) <> numrange(3.5,4.4);
 ?column?
----------
 t
(1 row)
< less than SELECT int4range(1,15) < int4range(4,5);
 ?column?
----------
 t
(1 row)
> greater than SELECT int4range(1,15) > int4range(1,8);
 ?column?
----------
 t
(1 row)
<= less than or equal SELECT numrange(3.1,5.2) <= numrange(3.1,5.2);
 ?column?
----------
 t
(1 row)
>= greater than or equal SELECT numrange(3.1,5.2) >= numrange(3.1,5.0);
 ?column?
----------
 t
(1 row)
@> contains range SELECT int4range(3,5) @> int4range(3,4);
 ?column?
----------
 t
(1 row)
@> contains element SELECT '[2014-01-01,2014-04-01)'::tsrange @> '2014-03-10'::timestamp;
 ?column?
----------
 t
(1 row)
<@ range is contained by SELECT int4range(3,5) <@ int4range(2,8);
 ?column?
----------
 t
(1 row)
<@ element is contained by SELECT 53 <@ int4range(1,7);
 ?column?
----------
 f
(1 row)
&& overlap (have points in common) SELECT int8range(4,8) && int8range(5,13);
 ?column?
----------
 t
(1 row)
<< strictly left of SELECT int8range(3,10) << int8range(105,120);
 ?column?
----------
 t
(1 row)
>> strictly right of SELECT int8range(30,50) >> int8range(20,30);
 ?column?
----------
 t
(1 row)
&< does not extend to the right of SELECT int8range(5,15) &< int8range(17,23);
 ?column?
----------
 t
(1 row)
&> does not extend to the left of SELECT int8range(6,22) &> int8range(4,8);
 ?column?
----------
 t
(1 row)
-|- is adjacent to SELECT numrange(1.7,2.6) -|- numrange(2.6,4.3);
 ?column?
----------
 t
(1 row)
+ union SELECT numrange(7,17) + numrange(10,20);
	  
 ?column?
----------
 [7,20)
(1 row)	  
* intersection SELECT int8range(7,17) * int8range(10,20);
 ?column?
----------
 [10,17)
(1 row)
- difference SELECT int8range(7,17) - int8range(10,20);
 ?column?
----------
 [7,10)
(1 row)

The comparison operators <, >, <=, and >= first compare the lower bounds first, and compare the upper bounds those who are equal to the 1st comparison.

The left-of/right-of/adjacent operators always return false while specifying an empty range is; 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 sub-ranges, as such a range cannot be represented.

Range Functions

lower() function

This function returns lower bound of a range specified in the argument.

Syntax :

lower(anyrange)

Return Type : range's element type

Example

  1. SELECT lower(numrange(2.3,4.6));  

Here is the result

 lower
-------
   2.3
(1 row)

upper() function

This function returns the upper bound of a range specified in the argument.

Syntax :

upper(anyrange)

Return Type : range's element type

Example

  1. SELECT upper(numrange(2.3,4.6));  

Here is the result

 upper
-------
   4.6
(1 row)

isempty() function

This function checks whether the specified the range is empty or not and returns a boolean value.

Syntax :

isempty(anyrange)

Return Type : boolean

Example

  1. SELECT isempty(numrange(2.3,4.6));  

Here is the result

 isempty
---------
 f
(1 row)

lower_inc() function

This function is used to check whether the lower bound inclusive or not within the specified range and return a boolean value.

Syntax :

lower_inc(anyrange)

Return Type : boolean

Example

  1. SELECT lower_inc(numrange(2.3,4.6));  

Here is the result

 lower_inc
-----------
 t
(1 row)

upper_inc() function

is the upper bound inclusive?

This function is used to check whether the upper bound is inclusive or not within the specified range and return a boolean value.

Syntax :

upper_inc(anyrange)

Return Type : boolean

Example

  1. SELECT upper_inc(numrange(2.3,4.6);  

Here is the result

 upper_inc
-----------
 f
(1 row)

lower_inf() function

This function is used to check whether the lower bound is infinite or not within the specified range and return a boolean value.

Syntax :

lower_inf(anyrange)

Return Type : boolean

Example

  1. SELECT lower_inf('(,)'::daterange);  

Here is the result

 lower_inf
-----------
 t
(1 row)

upper_inf() function

This function is used to check whether the upper bound is infinite or not within the specified range and return a boolean value.

Syntax :

upper_inf(anyrange)

Return Type : bolean

Example

  1. SELECT upper_inf('(,)'::daterange);  

Here is the result

 upper_inf
-----------
 t
(1 row)Return

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.