PostgreSQL 的 Range 区间类型

PostgreSQL 支持一种名为 Range 的类型，用以表示一段区间。Range 相当于一个抽象类型，我们可以实现自己的 Range 类型。PostgreSQL 内置实现了6种 Range 类型：

int4range –整数范围（int）
int8range –整数范围（bigint）
numrange – 数字范围
tsrange – 日期时间范围
tstzrange – 日期时间范围（含时区）
daterange – 日期范围以往在别的数据库系统我们需要使用2个字段作范围判断的话，在 PostgreSQL 里面会变得简单得多。比如一个房间预订系统：

1
2
3
4


CREATE TABLE reservation (
  room int,
  during tsrange
)

然后有以下数据：

1

INSERT INTO reservation VALUES ( 1008, '[2015-04-25 16:00, 2015-04-25 17:00)' ); # (代表区间不包括该下标,[代表包括。跟数字上的区间表达是一致的。

这时如果有别人要预订1008我们就可以查询该段时间内是否可用：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


SELECT during && '[2015-04-25 16:30, 2015-04-25 17:30)' FROM reservation WHERE room = 1008;
  # true && 判断两个范围之间是否有交集
SELECT during @> '2015-04-25 17:00' FROM reservation WHERE room = 1008;
  # false  @> 判断范围是否包含了该具体值
SELECT during * '[2015-04-25 14:00, 2015-04-25 16:30)' FROM reservation WHERE room = 1008
  # [2015-04-25 16:00, 2015-04-25 16:30) 计算2个区间的交集
SELECT upper(during) FROM reservation WHERE room = 1008;
  # 2015-04-25 17:00 取得区间上标
SELECT isempty(during) FROM reservation WHERE room = 1008;
  # false 判断区间是否为空集

在 PostgreSQL Shell 输入以下命令：

1
2


SELECT '(3, 9)'::int4range;  # [4,9)
SELECT '(3, 9]'::int4range;  # [4,10)

可以看到我们输入的值会被系统自动转换成统一的格式，这是因为像 int4range 等值属于离散区间。这个是指这个区间的值之间是不连续的，我们可以从某一个具体值推算出下一个元素的值会是怎么样子的。比如说 int 类型 3 之后一定是4。反过来讲，像是 float 类型是无法这样推算的，下一个值可能是 3.1， 3.11， 3.2 等等… 总而言之，连续的区间里面元素个数是可以无穷尽的。离散的区间元素个数总是可以确定的。在PostgreSQL中规定离形区间要定义一个 canonicalization function ，也就是一个标准化函数对所有输入的区间数值进行转化，使其总是输出一个特定的格式，这样做的目的是我们后期在使用数据库的时候可以正确地判断两个区间是否一致，这样一来，即使输入风格不一致，得出的结果仍然会是正确的：

1

select '[4,9]'::int4range = '[4,10)'::int4range;   # true

反过来说，连续区间就没有必要进行统一化格式，不同形式的定义即代表了不同的区间范围。

关于索引

Range 类型建议使用 GiST 或 SP-GiST 类型索引，当进行以下比较操作时系统会引优先引用该类索引加速查询：

=, &&, <@, @>, «,», -|-, &<, and &>

1

CREATE INDEX idx_reservation ON reservation USING gist (during);

关于约束

对于区间类型，UNIQUE 约束意义不大。更多时间我们需要的是某种不重复的约束。比如说时间段不能重复。Postgre 提供了 EXCLUDE 关键字用来达成这样的目的。

1

CREATE TABLE durings ( during tsrange, EXCLUDE USING gist (during WITH &&) );

这样在往表中插入有重合的行时就会引发异常。这可比我们在程序中自己判断再插入安全多了，也比自己写存储过程去锁表啥的方便。PostgreSQL 号称 Most Advanced 也是有道理的。

Contents