范围函数和操作符
范围操作符
- =
示例:
1 2 3 4 5
openGauss=# SELECT int4range(1,5) = '[1,4]'::int4range AS RESULT; result -------- t (1 row)
- <>
示例:
1 2 3 4 5
openGauss=# SELECT numrange(1.1,2.2) <> numrange(1.1,2.3) AS RESULT; result -------- t (1 row)
- <
示例:
1 2 3 4 5
openGauss=# SELECT int4range(1,10) < int4range(2,3) AS RESULT; result -------- t (1 row)
- >
示例:
1 2 3 4 5
openGauss=# SELECT int4range(1,10) > int4range(1,5) AS RESULT; result -------- t (1 row)
- <=
示例:
1 2 3 4 5
openGauss=# SELECT numrange(1.1,2.2) <= numrange(1.1,2.2) AS RESULT; result -------- t (1 row)
- >=
示例:
1 2 3 4 5
openGauss=# SELECT numrange(1.1,2.2) >= numrange(1.1,2.0) AS RESULT; result -------- t (1 row)
- @>
示例:
1 2 3 4 5
openGauss=# SELECT int4range(2,4) @> int4range(2,3) AS RESULT; result -------- t (1 row)
- @>
示例:
1 2 3 4 5
openGauss=# SELECT '[2011-01-01,2011-03-01)'::tsrange @> '2011-01-10'::timestamp AS RESULT; result -------- t (1 row)
- <@
示例:
1 2 3 4 5
openGauss=# SELECT int4range(2,4) <@ int4range(1,7) AS RESULT; result -------- t (1 row)
- <@
示例:
1 2 3 4 5
openGauss=# SELECT 42 <@ int4range(1,7) AS RESULT; result -------- f (1 row)
- &&
示例:
1 2 3 4 5
openGauss=# SELECT int8range(3,7) && int8range(4,12) AS RESULT; result -------- t (1 row)
- <<
示例:
1 2 3 4 5
openGauss=# SELECT int8range(1,10) << int8range(100,110) AS RESULT; result -------- t (1 row)
- >>
示例:
1 2 3 4 5
openGauss=# SELECT int8range(50,60) >> int8range(20,30) AS RESULT; result -------- t (1 row)
- &<
示例:
1 2 3 4 5
openGauss=# SELECT int8range(1,20) &< int8range(18,20) AS RESULT; result -------- t (1 row)
- &>
示例:
1 2 3 4 5
openGauss=# SELECT int8range(7,20) &> int8range(5,10) AS RESULT; result -------- t (1 row)
- -|-
示例:
1 2 3 4 5
openGauss=# SELECT numrange(1.1,2.2) -|- numrange(2.2,3.3) AS RESULT; result -------- t (1 row)
- +
示例:
1 2 3 4 5
openGauss=# SELECT numrange(5,15) + numrange(10,20) AS RESULT; result -------- [5,20) (1 row)
- *
示例:
1 2 3 4 5
openGauss=# SELECT int8range(5,15) * int8range(10,20) AS RESULT; result --------- [10,15) (1 row)
- -
示例:
1 2 3 4 5
openGauss=# SELECT int8range(5,15) - int8range(10,20) AS RESULT; result -------- [5,10) (1 row)
简单的比较操作符<,>,<=和>=先比较下界,只有下界相等时才比较上界。
<<、>>和-|-操作符当包含空范围时也会返回false;也就是,不认为空范围在其他范围之前或之后。
并集和差集操作符的执行结果无法包含两个不相交的子范围。
范围函数
如果范围是空或者需要的界限是无穷的,lower和upper函数将返回null。lower_inc、upper_inc、lower_inf和upper_inf函数均对空范围返回false。
- numrange(numeric, numeric, [text])
返回类型:范围元素类型
示例:
1 2 3 4 5 6 7 8 9 10
openGauss=# SELECT numrange(1.1,2.2) AS RESULT; result -------- [1.1,2.2) (1 row) openGauss=# SELECT numrange(1.1,2.2, '()') AS RESULT; result -------- (1.1,2.2) (1 row)
- lower(anyrange)
返回类型:范围元素类型
示例:
1 2 3 4 5
openGauss=# SELECT lower(numrange(1.1,2.2)) AS RESULT; result -------- 1.1 (1 row)
- upper(anyrange)
返回类型:范围元素类型
示例:
1 2 3 4 5
openGauss=# SELECT upper(numrange(1.1,2.2)) AS RESULT; result -------- 2.2 (1 row)
- isempty(anyrange)
返回类型:Boolean
示例:
1 2 3 4 5
openGauss=# SELECT isempty(numrange(1.1,2.2)) AS RESULT; result -------- f (1 row)
- lower_inc(anyrange)
返回类型:Boolean
示例:
1 2 3 4 5
openGauss=# SELECT lower_inc(numrange(1.1,2.2)) AS RESULT; result -------- t (1 row)
- upper_inc(anyrange)
返回类型:Boolean
示例:
1 2 3 4 5
openGauss=# SELECT upper_inc(numrange(1.1,2.2)) AS RESULT; result -------- f (1 row)
- lower_inf(anyrange)
返回类型:Boolean
示例:
1 2 3 4 5
openGauss=# SELECT lower_inf('(,)'::daterange) AS RESULT; result -------- t (1 row)
- upper_inf(anyrange)
返回类型:Boolean
示例:
1 2 3 4 5
openGauss=# SELECT upper_inf('(,)'::daterange) AS RESULT; result -------- t (1 row)