Help Center/ GaussDB(DWS)/ SQL Syntax Reference/ SQL Syntax Reference (9.1.0.x)/ Functions and Operators/ Range Functions and Operators/ Range Operators
Updated on 2024-09-02 GMT+08:00
View PDF
Share

Range Operators

=

Description: Equals

Example:

1
2
3
4
5
 
SELECT int4range(1,5) = '[1,4]'::int4range AS RESULT;
 result
--------
 t
(1 row)

<>

Description: Does not equal to

Example:

1
2
3
4
5
 
SELECT numrange(1.1,2.2) <> numrange(1.1,2.3) AS RESULT;
 result
--------
 t
(1 row)

<

Description: Is less than

Example:

1
2
3
4
5
 
SELECT int4range(1,10) < int4range(2,3) AS RESULT;
 result
--------
 t
(1 row)

>

Description: Is greater than

Example:

1
2
3
4
5
 
SELECT int4range(1,10) > int4range(1,5) AS RESULT;
 result
--------
 t
(1 row)

<=

Description: Is less than or equals

Example:

1
2
3
4
5
 
SELECT numrange(1.1,2.2) <= numrange(1.1,2.2) AS RESULT;
 result
--------
 t
(1 row)

>=

Description: Is greater than or equals

Example:

1
2
3
4
5
 
SELECT numrange(1.1,2.2) >= numrange(1.1,2.0) AS RESULT;
 result
--------
 t
(1 row)

@>

Description: The object on the left includes the object on the right.

Example:

1
2
3
4
5
 
SELECT int4range(2,4) @> int4range(2,3) AS RESULT;
 result
--------
 t
(1 row)

1
2
3
4
5
 
SELECT '[2011-01-01,2011-03-01)'::tsrange @> '2011-01-10'::timestamp AS RESULT;
 result
--------
 t
(1 row)

<@

Description: The object on the right includes the object on the left.

Example:

1
2
3
4
5
 
SELECT int4range(2,4) <@ int4range(1,7) AS RESULT;
 result
--------
 t
(1 row)

1
2
3
4
5
 
SELECT 42 <@ int4range(1,7) AS RESULT;
 result
--------
 f
(1 row)

&&

Description: Overlap (have points in common)

Example:

1
2
3
4
5
 
SELECT int8range(3,7) && int8range(4,12) AS RESULT;
 result
--------
 t
(1 row)

<<

Description: Strictly left of

Example:

1
2
3
4
5
 
SELECT int8range(1,10) << int8range(100,110) AS RESULT;
 result
--------
 t
(1 row)

>>

Description: Strictly right of

Example:

1
2
3
4
5
 
SELECT int8range(50,60) >> int8range(20,30) AS RESULT;
 result
--------
 t
(1 row)

&<

Description: Does not extend to the right of

Example:

1
2
3
4
5
 
SELECT int8range(1,20) &< int8range(18,20) AS RESULT;
 result
--------
 t
(1 row)

&>

Description: Does not extend to the left of

Example:

1
2
3
4
5
 
SELECT int8range(7,20) &> int8range(5,10) AS RESULT;
 result
--------
 t
(1 row)

-|-

Description: Is adjacent to

Example:

1
2
3
4
5
 
SELECT numrange(1.1,2.2) -|- numrange(2.2,3.3) AS RESULT;
 result
--------
 t
(1 row)

+

Description: Union

Example:

1
2
3
4
5
 
SELECT numrange(5,15) + numrange(10,20) AS RESULT;
 result 
--------
 [5,20)
(1 row)

*

Description: Intersection

Example:

1
2
3
4
5
 
SELECT int8range(5,15) * int8range(10,20) AS RESULT;
 result  
---------
 [10,15)
(1 row)

-

Description: Difference

Example:

1
2
3
4
5
 
SELECT int8range(5,15) - int8range(10,20) AS RESULT;
 result 
--------
 [5,10)
(1 row
  • The simple comparison operators <, >, <=, and >= compare the lower bounds first, and only if those are equal, compare the upper bounds.
  • The <<, >>, and -|- 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 sub-ranges.