Postgresql 中文操作指南

8.17. Range Types #

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

Range types are data types representing a range of values of some element type (called the range’s subtype). For instance, ranges of timestamp might be used to represent the ranges of time that a meeting room is reserved. In this case the data type is tsrange (short for “timestamp range”), and timestamp is the subtype. The subtype must have a total order so that it is well-defined whether element values are within, before, or after a range of values.

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

Range types are useful because they represent many element values in a single range value, and because concepts such as overlapping ranges can be expressed clearly. The use of time and date ranges for scheduling purposes is the clearest example; but price ranges, measurement ranges from an instrument, and so forth can also be useful.

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

Every range type has a corresponding multirange type. A multirange is an ordered list of non-contiguous, non-empty, non-null ranges. Most range operators also work on multiranges, and they have a few functions of their own.

8.17.1. Built-in Range and Multirange Types #

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

PostgreSQL comes with the following built-in range types:

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

In addition, you can define your own range types; see CREATE TYPE for more information.

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 。

See Table 9.55 and Table 9.57 for complete lists of operators and functions on range types.

8.17.3. Inclusive and Exclusive Bounds #

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

Every non-empty range has two bounds, the lower bound and the upper bound. All points between these values are included in the range. An inclusive bound means that the boundary point itself is included in the range as well, while an exclusive bound means that the boundary point is not included in the range.

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

In the text form of a range, an inclusive lower bound is represented by “[” while an exclusive lower bound is represented by “(”. Likewise, an inclusive upper bound is represented by “]”, while an exclusive upper bound is represented by “)”. (See Section 8.17.5 for more details.)

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

The functions lower_inc and upper_inc test the inclusivity of the lower and upper bounds of a range value, respectively.

8.17.4. Infinite (Unbounded) Ranges #

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

The lower bound of a range can be omitted, meaning that all values less than the upper bound are included in the range, e.g., (,3]. Likewise, if the upper bound of the range is omitted, then all values greater than the lower bound are included in the range. If both lower and upper bounds are omitted, all values of the element type are considered to be in the range. Specifying a missing bound as inclusive is automatically converted to exclusive, e.g., [,] is converted to (,). You can think of these missing values as +/-infinity, but they are special range type values and are considered to be beyond any range element type’s +/-infinity values.

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

Element types that have the notion of “infinity” can use them as explicit bound values. For example, with timestamp ranges, [today,infinity) excludes the special timestamp value infinity, while [today,infinity] include it, as does [today,) and [today,].

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

The functions lower_inf and upper_inf test for infinite lower and upper bounds of a range, respectively.

8.17.5. Range Input/Output #

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

The input for a range value must follow one of the following patterns:

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

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

The parentheses or brackets indicate whether the lower and upper bounds are exclusive or inclusive, as described previously. Notice that the final pattern is empty, which represents an empty range (a range that contains no points).

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

The lower-bound may be either a string that is valid input for the subtype, or empty to indicate no lower bound. Likewise, upper-bound may be either a string that is valid input for the subtype, or empty to indicate no upper bound.

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

Each bound value can be quoted using " (double quote) characters. This is necessary if the bound value contains parentheses, brackets, commas, double quotes, or backslashes, since these characters would otherwise be taken as part of the range syntax. To put a double quote or backslash in a quoted bound value, precede it with a backslash. (Also, a pair of double quotes within a double-quoted bound value is taken to represent a double quote character, analogously to the rules for single quotes in SQL literal strings.) Alternatively, you can avoid quoting and use backslash-escaping to protect all data characters that would otherwise be taken as range syntax. Also, to write a bound value that is an empty string, write "", since writing nothing means an infinite bound.

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

Whitespace is allowed before and after the range value, but any whitespace between the parentheses or brackets is taken as part of the lower or upper bound value. (Depending on the element type, it might or might not be significant.)

Note

这些规则与在复合类型文本中编写字段值规则非常相似。请参阅 Section 8.16.6以获取补充说明。

These rules are very similar to those for writing field values in composite-type literals. See Section 8.16.6 for additional commentary.

示例：

Examples:

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

一个多重范围的输入是包含零个或更多有效范围的大括号 ({ 和 })，这些范围用逗号隔开。括号和逗号周围允许空格。这旨在让人联想到数组语法，尽管多重范围更简单：它们只有一个维度，并且没有必要引用它们的内容。（不过，它们范围的边界可以如上所示引用。）

The input for a multirange is curly brackets ({ and }) containing zero or more valid ranges, separated by commas. Whitespace is permitted around the brackets and commas. This is intended to be reminiscent of array syntax, although multiranges are much simpler: they have just one dimension and there is no need to quote their contents. (The bounds of their ranges may be quoted as above however.)

示例：

Examples:

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

8.17.6. Constructing Ranges and Multiranges #

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

Each range type has a constructor function with the same name as the range type. Using the constructor function is frequently more convenient than writing a range literal constant, since it avoids the need for extra quoting of the bound values. The constructor function accepts two or three arguments. The two-argument form constructs a range in standard form (lower bound inclusive, upper bound exclusive), while the three-argument form constructs a range with bounds of the form specified by the third argument. The third argument must be one of the strings “()”, “(]”, “[)”, or “[]”. For example:

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

每种范围类型还具有与多重范围类型同名的多重范围构造函数。构造函数采用零个或更多参数，它们都是相应类型的范围。例如：

Each range type also has a multirange constructor with the same name as the multirange type. The constructor function takes zero or more arguments which are all ranges of the appropriate type. For example:

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 的精度有限，因此理论上可以将其视为离散的，但最好将其视为连续的，因为步长通常不重要。）

A discrete range is one whose element type has a well-defined “step”, such as integer or date. In these types two elements can be said to be adjacent, when there are no valid values between them. This contrasts with continuous ranges, where it’s always (or almost always) possible to identify other element values between two given values. For example, a range over the numeric type is continuous, as is a range over timestamp. (Even though timestamp has limited precision, and so could theoretically be treated as discrete, it’s better to consider it continuous since the step size is normally not of interest.)

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

Another way to think about a discrete range type is that there is a clear idea of a “next” or “previous” value for each element value. Knowing that, it is possible to convert between inclusive and exclusive representations of a range’s bounds, by choosing the next or previous element value instead of the one originally given. For example, in an integer range type [4,8] and (3,9) denote the same set of values; but this would not be so for a range over numeric.

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

A discrete range type should have a canonicalization function that is aware of the desired step size for the element type. The canonicalization function is charged with converting equivalent values of the range type to have identical representations, in particular consistently inclusive or exclusive bounds. If a canonicalization function is not specified, then ranges with different formatting will always be treated as unequal, even though they might represent the same set of values in reality.

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

The built-in range types int4range, int8range, and daterange all use a canonical form that includes the lower bound and excludes the upper bound; that is, [). User-defined range types can use other conventions, however.

8.17.8. Defining New Range Types #

用户可以定义他们自己的范围类型。这样做的最常见原因是使用在内置范围类型中未提供的子类型上的范围。例如，要定义子类型 float8 的新范围类型：

Users can define their own range types. The most common reason to do this is to use ranges over subtypes not provided among the built-in range types. For example, to define a new range type of subtype float8:

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

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

由于 float8 没有有意义的“步长”，因此在这个示例中我们没有定义规范化函数。

Because float8 has no meaningful “step”, we do not define a canonicalization function in this example.

当您定义自己的范围时，您会自动获得一个相应的多重范围类型。

When you define your own range you automatically get a corresponding multirange type.

定义自己的范围类型还允许您指定要使用的不同的子类型 B 树操作符类或排序规则，以便更改确定哪些值落入给定范围的排序顺序。

Defining your own range type also allows you to specify a different subtype B-tree operator class or collation to use, so as to change the sort ordering that determines which values fall into a given range.

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

If the subtype is considered to have discrete rather than continuous values, the CREATE TYPE command should specify a canonical function. The canonicalization function takes an input range value, and must return an equivalent range value that may have different bounds and formatting. The canonical output for two ranges that represent the same set of values, for example the integer ranges [1, 7] and [1, 8), must be identical. It doesn’t matter which representation you choose to be the canonical one, so long as two equivalent values with different formattings are always mapped to the same value with the same formatting. In addition to adjusting the inclusive/exclusive bounds format, a canonicalization function might round off boundary values, in case the desired step size is larger than what the subtype is capable of storing. For instance, a range type over timestamp could be defined to have a step size of an hour, in which case the canonicalization function would need to round off bounds that weren’t a multiple of an hour, or perhaps throw an error instead.

此外，任何旨在与 GiST 或 SP-GiST 索引一起使用的范围类型都应该定义子类型差异或 subtype_diff 函数。（即使没有 subtype_diff，索引仍然可以工作，但如果没有提供差异函数，它的效率可能会比提供差异函数的效率低得多。）子类型差异函数采用子类型的两个输入值，并返回它们之间的差异（即，X 减去 Y），表示为 float8 值。在上面的示例中，基础 float8 减函数 float8mi 可以使用；但是对于任何其他子类型，都需要进行某种类型转换。也可能需要创造性地思考如何将差异表示为数字。在最大程度上，subtype_diff 函数应该与所选操作符类和排序规则隐含的排序顺序一致；也就是说，如果它的第一个参数根据排序顺序大于它的第二个参数，它的结果应该为正。

In addition, any range type that is meant to be used with GiST or SP-GiST indexes should define a subtype difference, or subtype_diff, function. (The index will still work without subtype_diff, but it is likely to be considerably less efficient than if a difference function is provided.) The subtype difference function takes two input values of the subtype, and returns their difference (i.e., X minus Y) represented as a float8 value. In our example above, the function float8mi that underlies the regular float8 minus operator can be used; but for any other subtype, some type conversion would be necessary. Some creative thought about how to represent differences as numbers might be needed, too. To the greatest extent possible, the subtype_diff function should agree with the sort ordering implied by the selected operator class and collation; that is, its result should be positive whenever its first argument is greater than its second according to the sort ordering.

subtype_diff 函数的一个不太简化的示例是：

A less-oversimplified example of a subtype_diff function is:

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 。

See CREATE TYPE for more information about creating range types.

8.17.9. Indexing #

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

GiST and SP-GiST indexes can be created for table columns of range types. GiST indexes can be also created for table columns of multirange types. For instance, to create a GiST index:

CREATE INDEX reservation_idx ON reservation USING GIST (during);

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

A GiST or SP-GiST index on ranges can accelerate queries involving these range operators: =, &&, <@, @>, <<,>>, -|-, &<, and &>. A GiST index on multiranges can accelerate queries involving the same set of multirange operators. A GiST index on ranges and GiST index on multiranges can also accelerate queries involving these cross-type range to multirange and multirange to range operators correspondingly: &&, <@, @>, <<,>>, -|-, &<, and &>. See Table 9.55 for more information.

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

In addition, B-tree and hash indexes can be created for table columns of range types. For these index types, basically the only useful range operation is equality. There is a B-tree sort ordering defined for range values, with corresponding < and > operators, but the ordering is rather arbitrary and not usually useful in the real world. Range types' B-tree and hash support is primarily meant to allow sorting and hashing internally in queries, rather than creation of actual indexes.

8.17.10. Constraints on Ranges #

尽管 UNIQUE 是标量值的自然约束，但通常不适用于范围类型。相反，排除约束通常更合适（请参阅 CREATE TABLE … CONSTRAINT … EXCLUDE）。排除约束允许在范围类型上指定诸如“不重叠”之类的约束。例如：

While UNIQUE is a natural constraint for scalar values, it is usually unsuitable for range types. Instead, an exclusion constraint is often more appropriate (see CREATE TABLE … CONSTRAINT … EXCLUDE). Exclusion constraints allow the specification of constraints such as “non-overlapping” on a range type. For example:

CREATE TABLE reservation (
    during tsrange,
    EXCLUDE USING GIST (during WITH &&)
);

此约束将防止表中存在任何重叠值：

That constraint will prevent any overlapping values from existing in the table at the same time:

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 之后，只有会议室号相等时，以下约束才会拒绝重叠的范围：

You can use the btree_gist extension to define exclusion constraints on plain scalar data types, which can then be combined with range exclusions for maximum flexibility. For example, after btree_gist is installed, the following constraint will reject overlapping ranges only if the meeting room numbers are equal:

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