Postgresql 中文操作指南

8.17. Range Types #

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

范围类型很有用，因为它们用单个范围值表示多个元素值，并且可以清楚地表示重叠范围等概念。为了计划目的使用时间和日期范围是最明显的示例；但价格范围、仪器的测量范围等等也可能是有用的。

每个范围类型都有一个对应的多重范围类型。多重范围是一个非连续、非空、非空的范围的有序列表。大多数范围运算符也可以在多重范围上工作，并且它们有自己的几个函数。

8.17.1. Built-in Range and Multirange Types #

PostgreSQL 包含以下内置范围类型：

此外，可以定义自己的范围类型；有关详细信息，请参见 CREATE TYPE 。

8.17.2. Examples #

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

有关范围类型的运算符和函数的完整列表，请参见 Table 9.55 和 Table 9.57 。

8.17.3. Inclusive and Exclusive Bounds #

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

在范围的文本形式中，包含下限用“ [ ”表示，而排他下限用“ ( ”表示。同样地，包含上限用“ ] ”表示，而排他上限用“ ) ”表示。（有关详细信息，请参见 Section 8.17.5 。）

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

8.17.4. Infinite (Unbounded) Ranges #

可省略范围的下限，表示上限以下的所有值都包含在范围内，例如 (,3] 。同样地，如果省略范围的上限，则下限以上的所有值都包含在范围内。如果省略下限和上限，则元素类型的所有值都视为在范围内。将缺失的边界指定为包含自动转换为排他，例如 [,] 转换为 (,) 。你可以将这些缺失的值视为正负无穷大，但它们是特殊的范围类型值，并且被认为超出了任何范围元素类型的正负无穷大值。

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

函数 lower_inf 和 upper_inf 分别测试范围的无穷下界和上界。

8.17.5. Range Input/Output #

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

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

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

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

每个界限值都可以使用 "（双引号）字符引用。如果界限值包含圆括号、方括号、逗号、双引号或反斜杠，这是必需的，因为这些字符将被视为范围语法的一部分。要在带引号的界限值中放置双引号或反斜杠，请在其前面加上反斜杠。（此外，带双引号的界限值中的双引号对将表示双引号字符，类似于 SQL 文本串中单引号的规则。）或者，您可以避免引用和使用反斜杠转义来保护将被视为范围语法的所有数据字符。此外，要编写一个为空字符串的界限值，请编写 ""，因为不写任何内容表示无穷界限。

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

Note

这些规则与在复合类型文本中编写字段值规则非常相似。请参阅 Section 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. Constructing Ranges and Multiranges #

每个范围类型都具有一个与范围类型同名的构造函数。使用构造函数通常比编写范围文字常量更方便，因为它无需对边界值进行额外的引用。构造函数接受两个或三个参数。二参数形式构造一个标准形式的范围（包含下限，排他上限），而三参数形式构造一个根据第三个参数指定的形式的范围。第三个参数必须是“ () ”、“ (] ”、“ [) ”或“ [] ”之一。例如：

-- 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. Discrete Range Types #

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

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

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

内置范围类型 int4range、int8range 和 daterange 都使用一种规范形式，包括下边界但不包括上边界；也就是说，[)。但是，用户定义的范围类型可以使用其他约定。

8.17.8. Defining New Range Types #

用户可以定义他们自己的范围类型。这样做的最常见原因是使用在内置范围类型中未提供的子类型上的范围。例如，要定义子类型 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. Indexing #

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

CREATE INDEX reservation_idx ON reservation USING GIST (during);

范围上的 GiST 或 SP-GiST 索引可以加速涉及以下范围运算符的查询：=、&&、<@、@>、<<、>>、-|-、&< 和 &>。多范围上的 GiST 索引可以加速涉及相同多范围运算符集的查询。范围上的 GiST 索引和多范围上的 GiST 索引还可以加速涉及以下跨类型范围到多范围和多范围到范围运算符的查询：&&、<@、@>、<<、>>、-|-、&< 和 &>。请参阅 Table 9.55 了解更多信息。

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

8.17.10. Constraints on Ranges #

尽管 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