8.17. 范围类型#

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. 范围约束

范围类型是表示某种元素类型（称为范围的子类型）的范围值的数据类型。例如，timestamp范围可用于表示会议室预订的时间范围。在这种情况下，数据类型是tsrange（“timestamp range”的缩写），timestamp是子类型。子类型必须具有一个全序，以便明确定义元素值是在一个值范围之内、之前还是之后。

范围类型非常有用，因为它们可以在一个范围值中表示许多元素值，并且可以清楚地表达诸如重叠范围之类的概念。最明显的示例是将时间和日期范围用于调度目的；但是，价格范围、仪器的测量范围等也可能很有用。

每个范围类型都有一个对应的多范围类型。多范围是有序的非连续、非空、非空范围列表。大多数范围运算符也适用于多范围，并且它们有自己的几个函数。

8.17.1. 内置范围和多范围类型#

PostgreSQL 具有以下内置范围类型

• int4range — integer 范围，int4multirange — 对应多范围

• int8range — bigint 范围，int8multirange — 对应多范围

• numrange — numeric 范围，nummultirange — 对应多范围

• tsrange — timestamp without time zone 范围，tsmultirange — 对应多范围

• tstzrange — timestamp with time zone 范围，tstzmultirange — 对应多范围

• daterange — date 范围，datemultirange — 对应多范围

此外，您可以定义自己的范围类型；有关详细信息，请参见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)');

-- 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.55和表 9.57。

8.17.3. 包含和排除边界#

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

在范围的文本形式中，包含下边界由“[”表示，而排除下边界由“(”表示。同样，包含上边界由“]”表示，而排除上边界由“)”表示。（有关详细信息，请参见第 8.17.5 节。）

函数lower_inc和upper_inc分别测试范围值的上下边界的包含性。

8.17.4. 无限（无界）范围#

可以省略范围的下限，这意味着小于上限的所有值都包含在范围内，例如，(,3]。同样，如果省略范围的上限，则大于下限的所有值都包含在范围内。如果省略下限和上限，则元素类型的所有值都被认为在范围内。将缺失的上限指定为包含自动转换为排除，例如，[,]转换为(,)。你可以将这些缺失值视为 +/- 无穷大，但它们是特殊范围类型值，并且被认为超出任何范围元素类型的 +/- 无穷大值。

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

函数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 文本字符串中单引号的规则。）或者，你可以避免引用并使用反斜杠转义来保护所有会被视为范围语法的其他数据字符。此外，要编写一个空字符串的边界值，请编写""，因为不写任何内容表示无穷大边界。

范围值前后允许有空格，但括号或方括号之间的任何空格都被视为下限或上限值的一部分。（根据元素类型，它可能重要或不重要。）

注意

这些规则与复合类型字面量中编写字段值规则非常相似。有关其他说明，请参阅第 8.16.6 节。

示例

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

8.17.6. 构造范围和多范围#

每个范围类型都有一个与范围类型同名的构造函数。使用构造函数通常比编写范围字面量常量更方便，因为它避免了对边界值进行额外引用的需要。构造函数接受两个或三个参数。双参数形式构造标准形式的范围（下限包括在内，上限不包括在内），而三参数形式构造具有第三个参数指定的边界形式的范围。第三个参数必须是字符串“()”、“(]”、“[)”或“[]”之一。例如

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

8.17.7. 离散范围类型#

离散范围是指其元素类型具有明确定义的“步长”的范围，例如integer或date。在这些类型中，当它们之间没有有效值时，可以说两个元素是相邻的。这与连续范围形成对比，在连续范围内，在两个给定值之间总是（或几乎总是）可以识别出其他元素值。例如，numeric类型的范围是连续的，timestamp类型的范围也是如此。（即使timestamp的精度有限，因此理论上可以将其视为离散的，但最好将其视为连续的，因为通常不关心步长大小。）

考虑离散范围类型的另一种方法是，对于每个元素值，都有一个明确的“下一个”或“上一个”值的观念。知道了这一点，就可以通过选择下一个或上一个元素值而不是最初给定的元素值，在范围边界的包含和不包含表示之间进行转换。例如，在整数范围类型中，[4,8]和(3,9)表示相同的值集；但对于 numeric 类型的范围，情况并非如此。

离散范围类型应具有一个规范化函数，该函数了解元素类型的所需步长大小。规范化函数负责将范围类型的等效值转换为具有相同表示形式，特别是始终包含或不包含边界。如果未指定规范化函数，则格式不同的范围将始终被视为不相等，即使它们实际上可能表示相同的值集。

内置范围类型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 树运算符类或校对规则，以便更改确定哪些值落入给定范围的排序顺序。

如果子类型被认为具有离散值而不是连续值，则CREATE TYPE命令应指定canonical函数。规范化函数采用输入范围值，并且必须返回可能具有不同界限和格式的等效范围值。对于表示相同值集的两个范围（例如整数范围[1, 7]和[1, 8)）的规范输出必须相同。您选择哪种表示形式作为规范形式并不重要，只要始终将具有不同格式的两个等效值映射到具有相同格式的相同值即可。除了调整包含/排除界限格式之外，规范化函数还可能舍入边界值，以防所需的步长大于子类型能够存储的步长。例如，可以将timestamp上的范围类型定义为具有一小时的步长，在这种情况下，规范化函数需要舍入不是一小时倍数的界限，或者可能改为引发错误。

此外，任何打算与 GiST 或 SP-GiST 索引配合使用的范围类型都应定义子类型差异或subtype_diff函数。（索引在没有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。

8.17.9. 索引#

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

CREATE INDEX reservation_idx ON reservation USING GIST (during);

范围上的 GiST 或 SP-GiST 索引可以加速涉及以下范围运算符的查询：=、&&、<@、@>、<<、>>、-|-、&<和&>。多范围上的 GiST 索引可以加速涉及同一组多范围运算符的查询。范围上的 GiST 索引和多范围上的 GiST 索引还可以加速涉及以下跨类型范围到多范围和多范围到范围运算符的查询：&&、<@、@>、<<、>>、-|-、&<和&>。有关更多信息，请参见表 9.55。

此外，还可以为范围类型的表列创建 B 树和哈希索引。对于这些索引类型，基本上唯一有用的范围操作是相等性。范围值定义了 B 树排序顺序，具有相应的<和>运算符，但排序相当武断，通常在现实世界中没有用处。范围类型的 B 树和哈希支持主要用于在查询中允许内部排序和哈希，而不是创建实际索引。

8.17.10. 范围约束#

虽然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