范围类型

8.17.1 内置范围与多范围类型

8.17.2 示例

8.17.3 包含边界与排除边界

8.17.4 无限（无界）范围

8.17.5 范围的输入与输出

8.17.6 构造范围与多范围

8.17.7 离散范围类型

8.17.8 定义新的范围类型

8.17.9 索引

8.17.10 范围约束

PostgreSQL内置以下范围类型：

此外，还可以定义自己的范围类型。详见CREATE TYPE。

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

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

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

-- Extract the upper bound
SELECT upper(int8range(15, 25));

-- Compute the intersection
SELECT int4range(10, 20) * int4range(15, 25);

-- Is the range empty?
SELECT isempty(numrange(1, 5));

范围类型的完整运算符与函数列表见表9.58和表9.60。

每个非空范围都有下界和上界，两者之间的所有点都包含在范围中。包含边界意味着边界点本身也属于范围；排除边界意味着边界点本身不属于范围。

在范围的文本形式中，包含下界用[表示，排除下界用(表示；包含上界用]表示，排除上界用)表示。详见8.17.5节。

函数lower_inc和upper_inc分别检查范围值的下界与上界是否为包含边界。

可以省略范围的下界，表示范围包含所有小于上界的值，例如(,3]。同样，省略上界表示包含所有大于下界的值。若上下界均省略，则元素类型的所有值都视为属于范围。将缺失的边界指定为包含边界，会自动转换为排除边界，例如[,]转换为(,)。可以把缺失的边界理解为正负无穷，但它们是范围类型的特殊值，位于元素类型自身的正负无穷值之外。

具有“无穷”概念的元素类型可以将无穷作为显式边界值。例如，对时间戳范围，[today,infinity)排除特殊时间戳值infinity，而[today,infinity]包含它；[today,)和[today,]也包含它。

函数lower_inf和upper_inf分别检查范围的下界和上界是否无限。

范围值的输入必须符合下列模式之一：

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

如前所述，圆括号或方括号表示上下界是否排除或包含。最后一种模式是empty，表示不包含任何点的空范围。

lower-bound可以是子类型的合法输入字符串，也可以留空表示无下界；upper-bound同样可以是子类型的合法输入字符串，或留空表示无上界。

每个边界值都可以用双引号"括起来。如果边界值含圆括号、方括号、逗号、双引号或反斜杠，就必须这样做，否则这些字符会被当作范围语法的一部分。在带引号的边界值中写双引号或反斜杠，应在前面加反斜杠。此外，在双引号包围的边界值中，一对双引号也表示一个双引号字符，类似SQL字符串中处理单引号的规则。另一种方式是不使用外围引号，而以反斜杠转义所有可能被当作范围语法的数据字符。若要表示空字符串边界值，应写""，因为完全留空意味着无限边界。

范围值前后允许有空白；但圆括号或方括号内部的任何空白，都被视为上下界值的一部分，是否有意义取决于元素类型。

示例：

-- 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;

多范围的输入形式是花括号{和}，内部包含零个或多个有效范围，并以逗号分隔。括号和逗号周围允许空白。这种设计类似数组语法，但多范围更简单：它只有一个维度，也无需对内部范围整体加引号，不过范围的边界值仍可按上面的规则加引号。

示例：

SELECT '{}'::int4multirange;
SELECT '{[3,7)}'::int4multirange;
SELECT '{[3,7), [8,9)}'::int4multirange;

每个范围类型都有与类型同名的构造函数。它通常比编写范围字面常量更方便，因为无需额外对边界值加引号。构造函数接受两个或三个参数：两个参数的形式构造标准范围，即包含下界、排除上界；三个参数的形式按第三个参数指定边界形式。第三个参数必须是()、(]、[)或[]之一。例如：

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

-- If the third argument is omitted, '[)' is assumed.
SELECT numrange(1.0, 14.0);

-- Although '(]' is specified here, on display the value will be converted to
-- canonical form, since int8range is a discrete range type (see below).
SELECT int8range(1, 14, '(]');

-- Using NULL for either bound causes the range to be unbounded on that side.
SELECT numrange(NULL, 2.2);

每个范围类型也有与对应多范围类型同名的多范围构造函数，接受零个或多个参数，每个参数都是适当类型的范围。例如：

SELECT nummultirange();
SELECT nummultirange(numrange(1.0, 14.0));
SELECT nummultirange(numrange(1.0, 14.0), numrange(20.0, 25.0));

离散范围的元素类型具有明确的“步长”，例如整数或日期。在这种类型中，如果两个元素之间没有其他合法值，就可以说它们相邻。连续范围则不同：两个给定值之间总能或几乎总能找到其他值。例如，numeric和timestamp上的范围是连续的。虽然timestamp精度有限，理论上可以按离散类型处理，但通常并不关心这个步长，因此将其视为连续类型更合适。

另一种理解方式是：离散类型中的每个元素都有明确的“下一个”或“上一个”值。因此，可以通过选择相邻值，在包含边界和排除边界的表示之间转换。例如，对整数范围，[4,8]和(3,9)表示同一集合；但对numeric范围并非如此。

离散范围类型应有一个理解元素类型目标步长的规范化函数。它负责让等价范围值使用相同表示，尤其应统一包含或排除边界的形式。如果没有指定规范化函数，即使两个范围实际表示同一集合，只要格式不同，就始终被视为不相等。

内置范围类型int4range、int8range和daterange都采用包含下界、排除上界的规范形式，即[)。用户自定义范围类型也可以使用其他约定。

用户可以定义自己的范围类型，最常见的原因是要为内置范围没有覆盖的子类型创建范围。例如，定义以float8为子类型的新范围类型：

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

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

因为float8没有有意义的“步长”，此例不定义规范化函数。

定义自己的范围类型后，会自动获得相应的多范围类型。

自定义范围类型也可以指定不同的子类型B-tree运算符类或排序规则，以改变决定哪些值属于范围的排序顺序。

若子类型应被视为离散而非连续值，CREATE TYPE应指定canonical函数。它接收一个范围值，返回等价范围值，但边界和格式可以不同。代表同一集合的两个范围，例如整数范围[1,7]与[1,8)，必须得到相同的规范输出。选用哪种表示并不重要，只要不同格式的等价范围始终映射到相同形式。除调整边界包含或排除的形式外，若目标步长比子类型能够存储的步长更大，函数还可能对边界值取整。例如，可以把时间戳范围定义为以小时为步长，此时规范化函数应将不在整小时处的边界取整，或者直接报错。

此外，要用于GiST或SP-GiST索引的范围类型应定义子类型差值函数subtype_diff。没有它，索引仍能工作，但效率可能明显降低。该函数接受两个子类型值，将它们的差，即X减Y，以float8返回。前例可以使用普通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。

可以为范围类型列创建GiST和SP-GiST索引，也可以为多范围类型列创建GiST索引。例如：

CREATE INDEX reservation_idx ON reservation USING GIST (during);

范围上的GiST或SP-GiST索引可以加速以下范围运算符的查询：=、&&、<@、@>、<<、>>、-|-、&<和&>。多范围的GiST索引支持同一组多范围运算符。范围和多范围的GiST索引还可加速范围与多范围之间的交叉类型运算：&&、<@、@>、<<、>>、-|-、&<及&>。更多信息见表9.58。

也可以为范围类型列创建B-tree和哈希索引，但这类索引基本上只有相等判断真正有用。范围值有B-tree排序顺序及相应的<、>运算符，但顺序较任意，通常在实际应用中价值不大。范围类型的B-tree和哈希支持主要用于查询内部的排序和哈希，而非创建实际索引。

UNIQUE适合标量值，但通常不适合范围类型。排除约束往往更合适，参见CREATE TABLE … CONSTRAINT … EXCLUDE。它可以表达范围“不得重叠”等约束。例如：

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