【PostgreSQL】从零开始:（三十二）数据类型-范围类型

范围类型

范围类型（range type）是一种数据类型，用于表示一个连续的整数序列。在编程语言中，范围类型常用于迭代循环和切片操作中。

范围类型通常由起始值、结束值和步长组成。起始值表示序列的起始位置，结束值表示序列的结束位置，步长表示元素之间的间隔。根据具体的编程语言，范围类型的起始值和结束值可以是任意的整数，步长可以是正整数、负整数或零。

范围类型是表示某种元素类型（称为范围的子类型）的值范围的数据类型。例如timestampts，范围可用于表示预订会议室的时间范围。在本例中，数据类型rangetimestamp为（“时间戳范围”的缩写），并且是子类型。子类型必须具有总顺序，以便明确定义元素值是在值范围内、之前还是之后。

范围类型非常有用，因为它们表示单个范围值中的许多元素值，并且可以清楚地表达重叠范围等概念。使用时间和日期范围进行调度是最明显的例子;但价格范围、仪器的测量范围等也很有用。

每个范围类型都有相应的多范围类型。多范围是非连续、非空、非 null 范围的有序列表。大多数范围操作员也从事多范围工作，并且它们具有自己的一些功能。

内置范围和多范围类型

PostgreSQL 附带以下内置范围类型：

int4range— 范围 ， — 对应的多量程integer int4 multirange

int8range— 范围 ， — 对应的多量程bigint int8 multirange

numrange— 范围 ， — 对应的多量程numeric num multirange

tsrange— 范围 ， — 对应的多量程timestamp without time zone ts multirange

tstzrange— 范围 ， — 对应的多量程timestamp with time zonets tz multirange

daterange— 范围 ， — 对应的多量程date date multirange

此外，您可以定义自己的范围类型;有关更多信息

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

包容性和排他性边界

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

在范围的文本形式中，独占下限由“[”表示，而独占下限由“（”表示。同样，独占上限用“]”表示，而独占上限用“）”表示。
函数和分别测试范围值的下限lower_inc和上限 upper_inc的包容性。

无限（无界）范围

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

范围输入/输出

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

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

如前所述，括号或括号表示下限和上限是排他性还是非独占性。请注意，最终的模式是 ，empty它表示一个空范围（不包含点的范围）。

下限可以是作为子类型的有效输入的字符串，也可以是空的，表示没有下限。同样，上限可以是作为子类型的有效输入的字符串，也可以是空的，以指示没有上限。

每个绑定值都可以使用（双引号）字符引用。如果绑定值包含括号、方括号、逗号、双引号或反斜杠，则这是必需的，否则这些字符将被视为范围语法的一部分。若要在带引号的绑定值中放置双引号或反斜杠，请在其前面加上反斜杠。（此外，双引号绑定值中的一对双引号用于表示双引号字符，类似于 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));

离散范围类型

离散范围是指其元素类型具有明确定义的“步骤”（如 或 ）的区域。在这些类型中，当两个元素之间没有有效值时，它们可以说是相邻的。这与连续范围形成鲜明对比，在连续范围中，始终（或几乎总是）可以识别两个给定值之间的其他元素值。例如integer，date，numeric，timestamp，类型上的范围是连续的，超过类型的范围也是连续的。（尽管精度有限，因此理论上可以被视为离散的，但最好将其视为连续的，因为步长通常不感兴趣。

考虑离散范围类型的另一种方法是，对每个元素值的“下一个”或“上一个”值有一个明确的概念。知道了这一点，就可以通过选择下一个或上一个元素值而不是最初给出的值，在范围边界的独占和独占表示之间进行转换。例如[4,8] (3,9)，在整数范围内，键入并表示同一组值;但对于数字范围来说，情况并非如此。

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

内置范围类型 int4range、int8range 和daterange 都使用包含下限并排除上限的规范形式;那是[)。但是，用户定义的范围类型可以使用其他约定。

定义新的范围类型

用户可以定义自己的范围类型。执行此操作的最常见原因是使用范围，而不是内置范围类型中未提供的子类型。例如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函数。（如果没有 ，索引仍将起作用，但其效率可能远低于提供差分函数时的效率。子类型差异函数接受子类型的两个输入值，并返回它们的差异（即 X 减去 Y）表示为一个值。在上面的示例中，可以使用常规减号运算符的函数;但对于任何其他子类型，都需要进行一些类型转换。可能还需要一些关于如何将差异表示为数字的创造性想法。在最大程度上，subtype_diff函数应与所选运算符类和排序规则所隐含的排序顺序一致;也就是说，根据float8排序顺序，只要它的第一个参数float8mi大于第二个参数float8，它的结果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;

索引

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

CREATE INDEX reservation_idx ON reservation USING GIST (during);

范围的 GiST 或 SP-GiST 索引可以加速涉及以下范围运算符的查询：=、&&、<@、@>、<<和 >>。多范围的 GiST 索引可以加速涉及同一组多范围运算符的查询。范围上的 GiST 索引和多范围上的 GiST 索引还可以相应地加速涉及以下跨类型范围到多范围和多范围到范围运算符的查询：-|-、&<、&>、&&、<@@>、<<、>>和。

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

范围约束

虽然是标量值的自然约束，但它通常不适用于范围类型。相反，排除约束通常更合适。排除约束允许在范围类型上指定约束，例如“非重叠”UNIQUE。例如：

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