下载掌阅APP，畅读海量书库
立即打开
本节介绍一个PostgreSQL数据库中特有的数据类型：Range类型，此类型可以进行范围快速搜索，因此在一些场景中非常有用。
Range类型是从PostgreSQL 9.2版本开始提供的一种特有的类型，用于表示范围，如一个整数的范围、一个时间的范围，而范围底下的基本类型（如整数、时间）被称为Range类型的subtype。
现在大家可能有一个很大的疑问，这个类型有什么用，为什么不直接用开始值和结束值来表示，还要造一个Range类型？下面将举例说明Range类型的用途。
假设我们有以下需求，某个IP地址库中记录了每个地区的IP地址范围，现在需要查询客户的IP地址属于哪个地区。该IP地址库的定义如下：
CREATE TABLE ipdb1( ip_begin inet, ip_end inet, area text, sp text);
如果我们要查询的是IP地址“115.195.180.105”属于哪个地区，则查询的SQL语句如下：
select * from ipdb1 where ip_begin <= '115.195.180.105'::inet and ip_end >= '115.195.180.105'::inet;
因为表上没有索引，所以会进行全表扫描，执行速度会很慢。此时可以在ip_begin和ip_end上建索引，代码如下：
create index idx_ipdb_ip_start on ipdb1(ip_begin); create index idx_ipdb_ip_end on ipdb1(ip_end);
在PostgreSQL中，上面的SQL查询可以使用到这两个索引，但都是分别扫描两个索引建位图，然后通过位图进行AND操作，执行计划如下：
osdba=# explain analyze verbose select * from ipdb1 where ip_begin <= '115.195.180.105'::inet and ip_end >= '115.195.180.105'::inet;
QUERY PLAN
--------------------------------------------------------------------------------
Bitmap Heap Scan on public.ipdb1 (cost=2761.49..9277.91 rows=49454 width=128).
. (actual time=53.446..112.780 rows=1 loops=1)
Output: ip_begin, ip_end, area, sp
Recheck Cond: (ipdb1.ip_end >= '115.195.180.105'::inet)
Filter: (ipdb1.ip_begin <= '115.195.180.105'::inet)
Rows Removed by Filter: 317435
Heap Blocks: exact=3070
-> Bitmap Index Scan on idx_ipdb_ip_end (cost=0.00..2749.13 rows=148361 wi.
.dth=0) (actual time=52.719..52.719 rows=317436 loops=1)
Index Cond: (ipdb1.ip_end >= '115.195.180.105'::inet)
Planning time: 0.204 ms
Execution time: 113.163 ms
(10 rows)
Time: 114.623 ms
从上面的执行计划来看，对索引进行范围扫描效率仍不太高，那么有没有更高效的查询方法呢？答案是肯定的，这就是使用Range类型，通过创建空间索引的方式来执行，下面来看如何使用Range类型及空间索引。
首先，创建类似的IP地址库表：
CREATE TYPE inetrange AS RANGE (subtype = inet);
CREATE TABLE ipdb2(
ip_range inetrange,
area text,
sp text);
insert into ipdb2 select ('[' || ip_begin || ',' || ip_end || ']') ::inetrange, area, sp from ipdb1;
然后创建GiST索引：
CREATE INDEX idx_ipdb2_ip_range ON ipdb2 USING gist (ip_range);
接下来就可以使用包含运算符“@>”来查找相应的数据了：
select * from ipdb1 where ip_range @> '115.195.180.105'::inet;
相应SQL的执行计划如下：
osdba=# explain analyze verbose select * from ipdb2 where ip_range @> '115.195.180.105'::inet;
QUERY PLAN
--------------------------------------------------------------------------------
Index Scan using idx_ipdb2_ip_range on public.ipdb2 (cost=0.29..8.30 rows=1 w.
.idth=52) (actual time=14.476..40.127 rows=1 loops=1)
Output: ip_range, area, sp
Index Cond: (ipdb2.ip_range @> '115.195.180.105'::inet)
Planning time: 0.431 ms
Execution time: 40.187 ms
(5 rows)
前面的SQL语句的cost值为“cost=2761.49..9277.91”，而现在变为“cost=0.29..8.30”，从中可以看到查询性能得到了很大的提高。
PostgreSQL中内置了一些常用的Range类型，这些类型不需要创建就可以直接使用，具体如下。
·int4range：4字节整数的范围类型。
·int8range：8字节大整数的范围类型。
·numrange：numeric的范围类型。
·tsrange：无时区的时间戳的范围类型。
·tstzrange：带时区的时间戳的范围类型。
·daterange：日期的范围类型。
还可以使用CREATE TYPE函数创建Range类型，创建Range类型的语法如下：
CREATE TYPE name AS RANGE ( SUBTYPE = subtype [ , SUBTYPE_OPCLASS = subtype_operator_class ] [ , COLLATION = collation ] [ , CANONICAL = canonical_function ] [ , SUBTYPE_DIFF = subtype_diff_function ] )
语法中的子项的说明如下。
·SUBTYPE=subtype：指定子类型。
·SUBTYPE_OPCLASS=subtype_operator_class：指定子类型的操作符。
·COLLATION=collation：指定排序规则。
·CANONICAL=canonical_function：如果要创建一个稀疏的Range类型而不是一个连续的Range类型，那么就需要定义此函数。
·SUBTYPE_DIFF=subtype_diff_function：定义子类型的差别函数。
创建Range类型的示例如下：
CREATE TYPE floatrange AS RANGE ( subtype = float8, subtype_diff = float8mi );
Range类型的输入格式如下：
·'[value1,value2]'。
·'[value1,value2)'。
·'(value1,value2]'。
·'(value1,value2)'。
·'empty'。
其中“(”“)”表示范围内不包括此元素，而“[”“]”表示范围内包括此元素。如果是稀疏类型的Range，其内部存储的格式为“'[value1,value2)'”。“'empty'”表示空，即范围内不包括任何内容。
下面通过一些示例来看Range类型的输出和输入。
int4range类型的示例如下：
osdba=# select '(0,6)'::int4range; int4range ----------- [1,6) (1 row) osdba=# select '[0,6)'::int4range; int4range ----------- [0,6) (1 row) osdba=# select '[0,6]'::int4range; int4range ----------- [0,7) (1 row) osdba=# select 'empty'::int4range; int4range ----------- empty (1 row)
从上面的示例中可以看出，int4range总是把输入的格式转换成“'[value1,value2)'”格式。稀疏类型的Range必须定义CANONICAL函数，以便用来将其转换成“'[value1,value2)'”格式来存储。
对于连续类型的Range，内部存储则是精确存储的，如numrange类型：
osdba=# select '[0,6]'::numrange; numrange ---------- [0,6] (1 row) osdba=# select '[0,6)'::numrange; numrange ---------- [0,6) (1 row) osdba=# select '(0,6]'::numrange; numrange ---------- (0,6] (1 row) osdba=# select '(0,6)'::numrange; numrange ---------- (0,6) (1 row)
Range类型还可以表示极值的区间，示例如下：
osdba=# select '[1,)'::int4range; int4range ----------- [1,) (1 row)
上面的“[1,]”就表示从1到int4可以表示的最大数值。
osdba=# select '[,1)'::int4range; int4range ----------- (,1) (1 row)
上面的“[,1]”指的是从int4类型可以表示的最小值到1的范围。
对于numrange，表示的是无穷大或无穷小，比如，下面表示从1到无穷大：
osdba=# select '[1,)'::numrange; numrange ---------- [1,) (1 row)
而下面表示的是从负无穷到1：
osdba=# select '[,1)'::numrange; numrange ---------- [1,) (1 row)
还可以使用Range类型的构造函数输入Range类型的值，Range类型的构造函数名称与类型名称相同，示例如下：
osdba=# select int4range(1,10,'[)'); int4range ----------- [1,10) (1 row) osdba=# select int4range(1,10,'()'); int4range ----------- [2,10) (1 row) osdba=# select int4range(1,10); int4range ----------- [1,10) (1 row)
Range类型支持的操作符见表5-35。
表5-35 Range类型支持的操作符
Range类型支持的函数见表5-36。
表5-36 Range类型支持的函数
在Range类型的列上可以创建GiST和SP-GiST索引，创建语法如下：
CREATE INDEX index_name ON table_name USING gist (range_column);
在SQL查询语句中，可以使用运算符“=”“&&”“<@”“@>”“<<”“>>”“-|-”“&<”“&>”来执行索引。
当然，在Range类型上也可以建BTree索引，但BTree索引是使用比较运算符的，通常只在对Range的值进行排序时使用。
在Range的列上也可以建立约束，使其范围永不重叠，示例如下：
CREATE TABLE rtest01 ( idrange int4range, EXCLUDE USING gist (idrange WITH &&) );
接下来插入数据：
osdba=# insert into rtest01 values(int4range '[1,5)'); INSERT 0 1 (1 row) osdba=# insert into rtest01 values(int4range '[4,5)'); ERROR: conflicting key value violates exclusion constraint "rtest01_idrange_excl" DETAIL: Key (idrange)=([4,5)) conflicts with existing key (idrange)=([1,5)).
从上面的示例中可以看出，当插入的数据与原数据的范围出现重叠时就会报错。
上面的约束条件只能限制在一个Range列上，如果是一个有两列的表，第一列是一个普通类型的列，第二列是Range类型，我们需要让第一列的值相等的行的第二列值也不重叠，这时就需要用另一个扩展模块btree_gist来实现此约束功能。
要使用此功能，需要先安装btree_gist模块，安装命令如下：
cd <postgres source code path/contrib/btree_gist make make install
下面来演示此功能：
CREATE EXTENSION btree_gist; CREATE TABLE rtest02( id int, idrange int4range, EXCLUDE USING gist (id WITH =, idrange WITH &&) );
现在插入数据：
osdba=# INSERT INTO rtest02 values(1, int4range '[1,5)'); INSERT 0 1 osdba=# INSERT INTO rtest02 values(2, int4range '[2,5)'); INSERT 0 1
此时，我们发现只要第一列id的值不相同，即使第二列idrange的范围重叠，系统也不会报错，但如果插入一个行，其id值与原有行相同，且idrange列的值也与此行idrange的范围重叠：
osdba=# INSERT INTO rtest02 values(1, int4range '[2,5)'); ERROR: conflicting key value violates exclusion constraint "rtest02_id_idrange_excl" DETAIL: Key (id, idrange)=(1, [2,5)) conflicts with existing key (id, idrange)=(1, [1,5)).
那么系统就会报约束错误。