Postgresql学习笔记之——数据类型之Range类型

一、Range类型介绍

Range类型是PG 9.2之后开始出现的一种特有的类型，用于表现范围，如一个整数的范围、一个时间的范围，而范围底下的基本类型（如整数、时间）则被成为Range类型的subtype。
Range数据类型可以更快的在范围条件查询中检索到数据。
例如：
某个IP地址库记录了每个地区的IP地址的范围，现在需要查询指定IP地址在那个地区，IP库记录表为如下结构：
create table tb_ip_range(
begin_ip inet,
end_ip inet,
area text,
sp text);
现在查询IP地址192.188.17.120在那个地区，对应的查询语句如下：
select * from tb_ip_range where begin_ip <= ‘192.188.17.120’::inet and end_id >= ‘192.188.17.120’::inet
虽然在表上的begin_ip和end_ip列都创建了索引，但是在Postgresql中，虽然SQL语句会走索引，但是查询方式时分别扫描两个索引建位图，然后通过位图进行and操作，对比SQL语句的执行计划，索引的范围扫描还不是最高效的。
Range类型时通过创建空间索引的方式执行SQL语句，例如：
创建类似的IP地址库表：

1.创建Range类型：
create type inetrange as Range (subtype=inet);

2.创建IP范围表：
create table tb_ip_range(
ip_range inetrange，
area text,
sp text);

3.在ip_range列上创建索引，然后通过包含运算符 ”@>“ 查找对应的数据：
select * from tb_ip_range where ip_range @> ‘192.188.17.120’::inet

查看对应的SQL语句的执行计划可以明显看到性能提高很多。

二、Range类型的创建

在Postgresql中已经内置了一些常用的Range类型，可以不用执行create type xxx as range来创建：
int4range : 4字节整数的范围类型
int8range : 8字节大整数的范围类型
numrange : numeric的范围类型
tsrange : 无时区的时间戳范围类型
tstzrange : 带时区的时间戳范围类型
daterange : 日期的范围类型

示例：

# 时间范围
CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');

# 整数是否包含在范围内
postgres=# select int4range(10,20) @> 3;
 ?column? 
----------
 f
(1 row)

postgres=# select int4range(10,20) @> 11;
 ?column? 
----------
 t
(1 row)

# 判断两个范围是否有交集，有true，没有false
postgres=# select numrange(11.1,22.2) && numrange(20.0,30.0);
 ?column? 
----------
 t
(1 row)

postgres=# select numrange(11.1,22.2) && numrange(23.0,30.0);
 ?column? 
----------
 f
(1 row)

# 使用uper提取范围的最大边界
postgres=# select upper(int8range(15,30));
 upper 
-------
    30
(1 row)

postgres=# select upper(int8range(15,35));
 upper 
-------
    35
(1 row)

# 提取两个范围共同的交集
postgres=# select int4range(10, 20) * int4range(15, 25);
 ?column? 
----------
 [15,20)
(1 row)

postgres=# select int4range(10, 30) * int4range(15, 25);
 ?column? 
----------
 [15,25)
(1 row)


postgres=# select isempty(numrange(1,5));
 isempty 
---------
 f
(1 row)

如果以上内置的range类型不符合你的要求，可以使用CREATE TYPE来创建自定义的Range类型，语法如下：
CREATE TYPE name AS RANGE(
SUBTYPE = subtype
[ , SUBYTPE_OPCLASS = subtype_operator_class ]
[ , CLLATION = collation ]
[ , CANONICAL = canonical_function ]
[ , SUBTYPE_DIFF = subtype_diff_function ]

说明：
1.SUBTYPE = subtype：指定子类型
2.SUBYTPE_OPCLASS = subtype_operator_class：指定子类型的操作符
3.CLLATION = collation：指定排序规则
4.CANONICAL = canonical_function：如果要创建一个稀疏的Range类型，而不是一个连续的Range类型，那就定义此函数
5.SUBTYPE_DIFF = subtype_diff_function：定义子类型的差别函数

示例：
CREATE TYPE floatrange AS RANGE(
SUBTYPE=float8;
SUBTYPE_DIFF=float8mi
);

三、Range类型的输入

Range类型的输入格式：
’(lower-bound,upper-bound)‘
‘(lower-bound,upper-bound]’
’[lower-bound,upper-bound)‘
‘[lower-bound,upper-bound]’
’empty‘

其中，”(“ 和 ”)“ 表示定义的范围不包括此元素，”[“ 和 ”]“ 表示定义的范围包括此元素，’empty‘ 表示空，空表示范围内不包含任何东西。
示例：

-- includes 3, does not include 7, and does include all points in between
SELECT '[3,7)'::int4range;

-- does not include either 3 or 7, but includes all points in between
SELECT '(3,7)'::int4range;

-- includes only the single point 4
SELECT '[4,4]'::int4range;

-- includes no points (and will be normalized to 'empty')
SELECT '[4,4)'::int4range;

每个范围类型都有一个与范围类型同名的构造函数。使用构造函数通常比编写范围文字常量更方便，因为它避免了对绑定值的额外引用。构造函数接受两个或三个参数。两个参数的形式以标准形式构造一个范围(下界包括，上界排除)，而三个参数的形式构造一个范围，其边界由第三个参数指定。第三个参数必须是字符串“()”、“()”、“()”或“[]”中的一个。例如:

-- The full form is: lower bound, upper bound, and text argument indicating inclusivity/exclusivity of bounds.
postgres=# select numrange(1.0, 14.0, '(]');
  numrange  
------------
 (1.0,14.0]
(1 row)

-- If the third argument is omitted, '[)' is assumed.
postgres=# select numrange(1.0, 14.0);
  numrange  
------------
 [1.0,14.0)
(1 row)

-- Although '(]' is specified here, on display the value will be converted to
-- canonical form, since int8range is a discrete range type (see below).
postgres=# select int8range(1, 14, '(]');
 int8range 
-----------
 [2,15)
(1 row)

Range类型还可以表示极值的区间，例如：

1.表示从1开始到int4可以表示的最大值：

postgres=# select '[1,)'::int4range;
 int4range 
-----------
 [1,)
(1 row)

2.表示从int4可以表示的最小值到1的范围：

postgres=# select '[,1)'::int4range;
 int4range 
-----------
 (,1)
(1 row)

3.对于numrange范围，可以表示无穷大或者无穷小，例如，从1到无穷大与负无穷到1：

postgres=# select '[1,)'::numrange;
 numrange 
----------
 [1,)
(1 row)

postgres=# select '[,1)'::numrange;
 numrange 
----------
 (,1)
(1 row)

注意：与numrange不同的是，int4range里不是无穷大或者负无穷，因为int4有具体的范围。

四、Range类型的操作符和函数

1.Range类型支持的操作符：

操作符	描述	例子	结果
=	等于	select int4range’[1,5)’ = ‘[1,4]’::int4range;	t
<>	不等于	select numrange(1.1,1.2) <> numrange(1.1,1.3);	t
<	小于	select int4range ‘[1,10)’ < int4range’[2,3)’;	t
>	大于	select int4range ‘[2,3)’ > int4range’[1,100)’;	t
<=	小于等于	select int4range’[2,3)’<= int4range’[1,2)’;	f
>=	大于等于	select int4range’[2,3)’>= int4range’[1,2)’;	t
@>	包含（左边包含了右边）	select int4range’[1,3)’ @> int4range’[1,2)’;	t
<@	被包含（右边包含左边）	select int4range’[1,2)’ <@ int4range’[1,4)’;	t
&&	重叠（两个范围有交集）	select int4range’[1,2)’ && int4range’[1,4)’;	t
<<	严格在左（没有重叠值）	select int4range’[1,2)’ << int4range’[2,4)’;	t
>>	严格在右	select int4range’[2,4)’ >> int4range’[1,2)’;	t
&<	没有扩展到右边	select int4range’[1,2)’ &< int4range’[1,4)’;	t
&>	没有扩展到左边	select int4range’[1,2)’ &> int4range’[1,4)’;	t
-l-	链接在一起（值没有交集）	select int4range’[1,2)’ -	- int4range’[2,4)’;
+	union(将两个范围合并在一起)	select int4range’[1,2)’ + int4range’[2,4)’;	[1,4)
*	intersection	select int4range’[1,4)’ * int4range’[2,5)’;	[2,4)
-	difference	select int4range’[1,4)’ - int4range’[2,5)’;	[1,2)

2.Range类型的函数：

1.lower(anyrange) : 获得范围的起始值

postgres=# select lower(int4range '[11,22)');
 lower 
-------
    11
(1 row)

postgres=# select lower(int4range '[11,22)') is null;;
 ?column? 
----------
 f
(1 row)

postgres=# select lower(int4range '[11,22)') is not null;
 ?column? 
----------
 t
(1 row)

2.upper(anyrange)：获得范围的结束值

postgres=# select upper(int4range '[11,22)');
 upper 
-------
    22
(1 row)

postgres=# select upper(int4range '[11,34]');
 upper 
-------
    35
(1 row)

注意：获取的范围结束值是不包含在范围内的最大值！

3.isempty(anyrange)：是否是空范围

postgres=# select isempty(int4range'empty');
 isempty 
---------
 t
(1 row)

postgres=# select isempty(int4range'(,)');
 isempty 
---------
 f
(1 row)

postgres=# select isempty(int4range'(1,1)');
 isempty 
---------
 t
(1 row)

postgres=# select isempty(int4range'[1,2)');
 isempty 
---------
 f
(1 row)

4.lower_inc(anyrange)：起始值是否在范围内

postgres=# select lower_inc(int4range'(1,2)');
 lower_inc 
-----------
 f
(1 row)

postgres=# select lower_inc(int4range'[1,2)');
 lower_inc 
-----------
 t
(1 row)

postgres=# select lower_inc(int4range'(1,4]');
 lower_inc 
-----------
 t
(1 row)

5.upper_inc(anyrange)：结束值是否在范围内

postgres=# select upper_inc(int4range'(1,2)');
 upper_inc 
-----------
 f
(1 row)

postgres=# select upper_inc(int4range'(1,3]');
 upper_inc 
-----------
 f
(1 row)

6.lower_inf(anyrange)：起始值是否是一个无穷值
7.upper_inf(anyrange)：结束值是否是一个无穷值

五、Range类型的索引和约束

1.索引：

可以为范围类型的表列创建GiST和SP-GiST索引。例如，创建一个GiST索引:

CREATE INDEX reservation_idx ON reservation USING GIST (during);

索引创建后可以在SQL语句中使用Range类型的操作符来进行索引数据检索。

此外，可以为范围类型的表列创建B-tree和hash索引。对于这些索引类型，基本上惟一有用的范围操作是相等操作。有一个为范围值定义的B-tree排序，具有相应的<和>操作符，但是排序是相当随意的，在现实世界中通常没有用处。Range类型的b -树和散列支持主要是为了允许在查询内部进行排序和散列，而不是创建实际的索引。

2.约束

在Range类型的列上一般创建排除约束，让其范围总是不重叠的，例如：

CREATE TABLE reservation (
    during tsrange,
    EXCLUDE USING GIST (during WITH &&)
);

INSERT INTO reservation VALUES
    ('[2010-01-01 11:30, 2010-01-01 15:00)');
INSERT 0 1

INSERT INTO reservation VALUES
    ('[2010-01-01 14:45, 2010-01-01 15:45)');
ERROR:  conflicting key value violates exclusion constraint "reservation_during_excl"
DETAIL:  Key (during)=(["2010-01-01 14:45:00","2010-01-01 15:45:00")) conflicts
with existing key (during)=(["2010-01-01 11:30:00","2010-01-01 15:00:00")).

由以上例子看出，列上创建了排他约束，插入数据时，如果与原有数据范围有重叠时就会报错。

如果有一个两个列的表，第一个列时普通类型是 id ，第二个时Range类型是 during ，插入数据时判断，如果 id 值相等时，如果Range列范围有重叠，插入报错，如果 id 值不相等，那么Range列即使范围重叠也不报错，可以进行数据插入。这时就需要Postgresql的扩展模块 btree_gist 来实现：

CREATE EXTENSION btree_gist;
CREATE TABLE room_reservation (
    id intege,
    during tsrange,
    EXCLUDE USING GIST (room WITH =, during WITH &&)
);

INSERT INTO room_reservation VALUES
    (1, '[2010-01-01 14:00, 2010-01-01 15:00)');
INSERT 0 1

INSERT INTO room_reservation VALUES
    (1, '[2010-01-01 14:30, 2010-01-01 15:30)');
ERROR:  conflicting key value violates exclusion constraint "room_reservation_room_during_excl"
DETAIL:  Key (room, during)=(123A, ["2010-01-01 14:30:00","2010-01-01 15:30:00")) conflicts
with existing key (room, during)=(123A, ["2010-01-01 14:00:00","2010-01-01 15:00:00")).

INSERT INTO room_reservation VALUES
    (2, '[2010-01-01 14:30, 2010-01-01 15:30)');
INSERT 0 1