8.17. 范围类型

8.17.1. 内建范围类型

PostgreSQL 带有下列内建范围类型：

• int4range — integer的范围

• int8range — bigint的范围

• numrange — numeric的范围

• tsrange — timestamp without time zone的范围

• tstzrange — timestamp with time zone的范围

• daterange — date的范围

此外，你可以定义自己的范围类型，更多信息见CREATE TYPE。

8.17.2. 例子

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

-- 包含
SELECT int4range(10, 20) @> 3;

-- 重叠
SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);

-- 提取上边界
SELECT upper(int8range(15, 25));

-- 计算交集
SELECT int4range(10, 20) * int4range(15, 25);

-- 范围为空吗？
SELECT isempty(numrange(1, 5));

范围类型的操作符和函数的完整列表见表 9-47 以及表 9-48。

8.17.3. 包含和排除边界

每一个非空范围都有两个边界，下界和上界。这两个值之间的所有点都包含在范围内。 包含边界意味着边界点本身也被包括在范围内，而排除边界意味着边界点不被包括在范围内。

在一个范围的文本形式中，包含下界表示为"["， 而排除下界表示为"("。同样， 包含上界表示为"]"， 而排除下界表示为")"（详见第 8.17.5 节）。

函数lower_inc和upper_inc 分别检测一个范围值的上下边界是否包含。

8.17.4. 无限（无界）范围

一个范围的下界可以被忽略，意味着小于上界的所有点都包括在范围中。同样， 如果范围的上界被忽略，那么所有大于下界的点都包括在范围中。如果上下界都被忽略， 该元素类型的所有值都被认为在该范围中。

这等效于把下界当作"负无穷"，或者把上界当作"正无穷"。 但是注意这些无穷值绝不是范围的元素类型的值，并且绝不是范围的一部分 （所以没有包含无穷边界之类的东西 — 如果你尝试写一个，它将被自动转换成一个排除界限）。

还有，有一些元素类型具有一种"无限"概念， 但是那只是范围类型机制所涉及的另一个值。例如，在时间戳范围中， [today,]意味着与[today,)相同的东西。 但是[today,infinity]意味着与[today,infinity) 不同的某种东西 — 后者排除了特殊的timestamp值infinity。

函数lower_inf和upper_inf 分别检测一个范围的无限下界和无限上界。

8.17.5. 范围输入/输出

一个范围值的输入必须遵循下列模式之一：

(lower-bound,upper-bound)
(lower-bound,upper-bound]
[lower-bound,upper-bound)
[lower-bound,upper-bound]
empty

如前所述，圆括号或方括号指示上下界是否为排除的或者包含的。 注意最后一个模式是empty，它表示一个空范围（一个不包含点的范围）。

lower-bound可以是子类型有效输入的一个字符串， 或者是空表示没有下界。同样，upper-bound 可以是子类型有效输入的一个字符串，或者是空表示没有上界。

每个界限值可以使用"（双引号）字符引用。如果界限值包含圆括号、 方括号、逗号、双引号或反斜线时，这样做是必须的，因为否则那些字符会被当作范围语法的一部分。 要把一个双引号或反斜线放在一个被引用的界限值中，就在它前面放一个反斜线 （还有，在一个双引号引用的界限值中的一对双引号表示一个双引号字符， 这与 SQL 字符串中的单引号规则类似）。此外， 你可以避免引用并且使用反斜线转义来保护所有数据字符，否则它们会被当做范围语法。还有， 要写一个是空字符串的界限值，则可以写成""，因为什么都不写表示一个无限界限。

范围值前后允许有空格，但是圆括号或方括号之间的任何空格会被当做上下界值的一部分 （重要还是不重要取决于元素类型。）。

例子：

-- 包括 3，不包括 7，并且包括 3 和 7 之间的所有点
SELECT '[3,7)'::int4range;

-- 既不包括 3 也不包括 7，但是包括两者之间的所有点
SELECT '(3,7)'::int4range;

-- 只包括单独一个点 4
SELECT '[4,4]'::int4range;

-- 不包括点（并且将被标准化为 '空'）
SELECT '[4,4)'::int4range;

8.17.6. 构造范围

每一种范围类型都有一个与其同名的构造器函数。 使用构造器函数往往比写一个范围文本常量更方便，因为它避免了对界限值的额外引用。 构造器函数接受两个或三个参数。两个参数的形式以标准的形式构造一个范围 （下界是包含的，上界是排除的），而三个参数的形式按照第三个参数指定的界限形式构造一个范围。 第三个参数必须是下列字符串之一： "()"、 "(]"、 "[)"或者 "[]"。 例如：

-- 完整形式是：下界、上界以及指示界限包含性/排除性的文本参数。
SELECT numrange(1.0, 14.0, '(]');

-- 如果第三个参数被忽略，则假定为 '[)'。
SELECT numrange(1.0, 14.0);

-- 尽管这里指定了 '(]'，显示时该值将被转换成标准形式，因为 int8range 是一种离散范围类型（见下文）。
SELECT int8range(1, 14, '(]');

-- 为一个界限使用 NULL 导致范围在那一边是无界的。
SELECT numrange(NULL, 2.2);

8.17.7. 离散范围类型

离散范围的元素类型具有一个良定义的"步长"， 例如integer或date。在这些类型中，如果两个元素之间没有有效值， 它们可以被说成是相邻。这与连续范围相反，连续范围中总是（或者几乎总是） 可以在两个给定值之间标识其他元素值。例如，numeric 类型的范围是连续的，timestamp的范围也是（ 尽管timestamp具有有限的精度，并且在理论上可以被当做离散的， 最好认为它是连续的，因为通常并不关心它的步长大小）。

另一种考虑离散范围类型的方法是对每一个元素值都有一个清晰的 "下一个"或"上一个"值。了解了这种思想之后， 通过选择原来给定的下一个或上一个元素值来取代它， 就可以在一个范围界限的包含和排除表达之间转换。例如，在一个整数范围类型中， [4,8]和(3,9)表示相同的值集合，但是对于数值上的范围就不是这样。

一个离散范围类型应该具有一个标准化函数，它知道元素类型期望的步长。 标准化函数负责把范围类型的等效值转换成同一表达方式，特别是与包含或者排除界限。 如果没有指定一个标准化函数，那么具有不同格式的范围将总是会被当作不等， 即使它们实际上是表达相同的一组值。

内建的范围类型int4range、int8range和 daterange都使用一种标准形式，该形式包括下界并且排除上界， 也就是[)。不过，用户定义的范围类型可以使用其他习惯。

8.17.8. 定义新的范围类型

用户可以定义他们自己的范围类型。 这样做最常见的原因是为了使用内建范围类型中没有提供的子类型范围。 例如，要创建一个子类型float8的新范围类型：

CREATE TYPE floatrange AS RANGE (
    subtype = float8,
    subtype_diff = float8mi
);

SELECT '[1.234, 5.678]'::floatrange;

因为float8没有有意义的"步长"， 我们在这个例子中没有定义一个标准化函数。

定义你自己的范围类型也允许你指定一个要使用的不同的子类型B-tree操作符类或者排序规则， 以便改变排序次序来决定哪些值落入一个给定的范围。

如果子类型被认为是具有离散值而不是连续值，CREATE TYPE 命令应当指定一个标准化函数。标准化函数接收一个输入的范围值， 并且必须返回一个可能具有不同界限和格式的等价的范围值。对于两个表示相同值集合的范围 （例如整数范围[1, 7]和[1, 8)），标准输出必须一样。 选择哪一种表达作为标准没有关系， 只要两个具有不同格式的等价值总是能被映射到具有相同格式的相同值就行。 除了调整包含/排除界限格式外，假使期望的步长大于子类型能够存储的值， 标准化函数可能会取整边界值。例如，一个timestamp 之上的范围类型可能被定义为具有一个一小时的步长， 这样标准化函数可能需要对不是一小时的倍数的界限进行舍入，或者可能直接抛出一个错误。

此外，任何打算要和 GiST 或 SP-GiST 索引一起使用的范围类型应当定义一个子类型差异， 或subtype_diff函数（没有subtype_diff时索引仍然能工作， 但是可能效率不如提供了差异函数时高）。子类型差异函数采用子类型的两个输入值， 并且返回它们之间表示为一个float8值的差（即X减Y）。 在我们上面的例子中，可以使用常规float8减法操作符之下的float8mi函数。 但是对于任何其他子类型，可能需要某种类型转换。 还可能需要一些关于如何把差异表示为数字的创新型想法。为了最大的可扩展性， subtype_diff函数应该与所选操作符类和排序规则所蕴含的排序顺序一致， 也就是说，根据排序顺序，当它的第一个参数大于第二个参数时，它的结果就应该是正值。

subtype_diff函数的一个简单例子是:

CREATE FUNCTION time_subtype_diff(x time, y time) RETURNS float8 AS
'SELECT EXTRACT(EPOCH FROM (x - y))' LANGUAGE sql STRICT IMMUTABLE;

CREATE TYPE timerange AS RANGE (
    subtype = time,
    subtype_diff = time_subtype_diff
);

SELECT '[11:10, 23:00]'::timerange;

更多关于创建范围类型的信息请参考CREATE TYPE。

8.17.9. 索引

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

CREATE INDEX reservation_idx ON reservation USING GIST (during);

一个 GiST 或 SP-GiST 索引可以加速涉及以下范围操作符的查询： =、 &&、 <@、 @>、 <<、 >>、 -|-、 &<以及 &> （详见表 9-47）。

此外，B-树和哈希索引可以在范围类型的表列上创建。对于这些索引类型， 基本上唯一有用的范围操作就是等值。使用相应的< 和 > 操作符，为范围值定义一个 B-树排序顺序， 但是该顺序相当任意并且在现实世界中通常没有用。 范围类型的 B-树和哈希支持主要是为了允许在查询内部进行排序和哈希，而不是创建真正的索引。

8.17.10. 范围上的约束

虽然UNIQUE是标量值的一种自然约束，它通常不适合于范围类型。 反而，一种排除约束常常更加适合 （见CREATE TABLE ... CONSTRAINT ... EXCLUDE）。 排除约束允许在一个范围类型上声明诸如"non-overlapping"的约束。例如：

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")).

你可以使用btree_gist 扩展来在纯标量数据类型上定义排除约束，然后把它和范围排除结合来获得最大的灵活性。 例如，安装btree_gist之后， 下列约束将仅在会议室房间号相等时拒绝重叠的范围：

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

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

INSERT INTO room_reservation VALUES
    ('123A', '[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
    ('123B', '[2010-01-01 14:30, 2010-01-01 15:30)');
INSERT 0 1