AnalyticDB:Range functions and operators

Operator

Description

Example

Sample result

=

Checks whether two ranges are equal.

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

t

<>

Checks whether two ranges are not equal.

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

t

<

Checks whether the range to the left of the operator is less than the range to the right of the operator.

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

t

>

Checks whether the range to the left of the operator is greater than the range to the right of the operator.

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

t

<=

Checks whether the range to the left of the operator is less than or equal to the range to the right of the operator.

numrange(1.1,2.2) <= numrange(1.1,2.2)

t

>=

Checks whether the range to the left of the operator is greater than or equal to the range to the right of the operator.

numrange(1.1,2.2) >= numrange(1.1,2.0)

t

@>

Checks whether the range to the left of the operator contains the range to the right of the operator.

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

t

@>

Checks whether the range to the left of the operator contains the element to the right of the operator.

'[2011-01-01,2011-03-01)'::tsrange @> '2011-01-10'::timestamp

t

<@

Checks whether the range to the left of the operator falls within the range to the right of the operator.

int4range(2,4) <@ int4range(1,7)

t

<@

Checks whether the element to the left of the operator falls within the range to the right of the operator.

42 <@ int4range(1,7)

f

&&

Checks whether two ranges overlap (have points in common).

int8range(3,7) && int8range(4,12)

t

<<

Checks whether the range to the left of the operator is strictly less than the range to the right of the operator with no overlap.

int8range(1,10) << int8range(100,110)

t

>>

Checks whether the range to the right of the operator is strictly less than the range to the left of the operator with no overlap.

int8range(50,60) >> int8range(20,30)

t

&<

Checks whether the range to the left of the operator does not exceed the upper bound of the range to the right of the operator.

int8range(1,20) &< int8range(18,20)

t

&>

Checks whether the range to the right of the operator does not exceed the upper bound of the range to the left of the operator.

int8range(7,20) &> int8range(5,10)

t

-|-

Checks whether two ranges are adjacent.

numrange(1.1,2.2) -|- numrange(2.2,3.3)

t

+

Returns the union of two ranges.

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

[5,20)

*

Returns the intersection of two ranges.

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

[10,15)

-

Subtracts the range to the right of the operator from the range to the left of the operator.

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

[5,10)

Function

Data type of the return value

Description

Example

Sample result

lower(anyrange)

Data type of the elements of the range

Returns the lower bound of a range.

lower(numrange(1.1,2.2))

1.1

upper(anyrange)

Data type of the elements of the range

Returns the upper bound of a range.

upper(numrange(1.1,2.2))

2.2

isempty(anyrange)

boolean

Checks whether a range is empty.

isempty(numrange(1.1,2.2))

false

lower_inc(anyrange)

boolean

Checks whether the lower bound of a range is inclusive.

lower_inc(numrange(1.1,2.2))

true

upper_inc(anyrange)

boolean

Checks whether the upper bound of a range is inclusive.

upper_inc(numrange(1.1,2.2))

false

lower_inf(anyrange)

boolean

Checks whether the lower bound of a range is infinite.

lower_inf('(,)'::daterange)

true

upper_inf(anyrange)

boolean

Checks whether the upper bound of a range is infinite.

upper_inf('(,)'::daterange)

true

range_merge(anyrange,anyrange)

anyrange

Returns the smallest range after merging two ranges.

range_merge('[1,2)'::int4range, '[3,4)'::int4range)

[1,4)