PostgreSQL Range Functions and Operators

Last update on November 13 2023 09:59:27 (UTC/GMT +8 hours)

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

Code:

SELECT lower(numrange(2.3,4.6));

Here is the result

Sample Output:

 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

Code:

SELECT upper(numrange(2.3,4.6));

Here is the result

Sample Output:

 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

Code:

SELECT isempty(numrange(2.3,4.6));

Here is the result

Sample Output:

 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

Code:

SELECT lower_inc(numrange(2.3,4.6));

Here is the result

Sample Output:

 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

Code:

SELECT upper_inc(numrange(2.3,4.6);

Here is the result

Sample Output:

 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

Code:

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

Here is the result

Sample Output:

 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

Code:

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

Here is the result

Sample Output:

 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.

Previous: JSON Functions and Operators
Next: XML Functions