8.17. 范围类型 · PostgreSQL 中文文档 9.3

# 8.17\. 范围类型范围数据类型代表着某一元素类型在一定范围内的值。 (此元素类型称为该范围的 _子类型_)。例如，`timestamp` 范围可能被用于代表一间会议室被预定的时间范围。这种情况下数据类型为`tsrange` ("timestamp range"的简写)，并且`timestamp` 是子类型。子类型必须具备完整的排序，这样清晰定义了元素值在范围之内，之前，或者之后。范围类型是有用的。因为他们代表了在单一范围内的许多元素值，并且清晰表达了诸如重叠范围等概念。出于计划目的的时间和日期范围的使用是一个最清晰的例子；价格范围，仪器测量的范围等也有用。 ## 8.17.1\. 内嵌范围类型 PostgreSQL 提供下列内嵌范围类型： * `int4range` — `integer`的范围 * `int8range` —`bigint`的范围 * `numrange` —`numeric`的范围 * `tsrange` —`timestamp without time zone`的范围 * `tstzrange` —`timestamp with time zone`的范围 * `daterange` —`date`的范围此外，你可以定义你自己的范围类型；更多信息见[CREATE TYPE](#calibre_link-100)。 ## 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)'); -- 包含 SELECT int4range(10, 20) @> 3; -- 重叠 SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0); -- 提取上边界 SELECT upper(int8range(15, 25)); -- 计算交叉 SELECT int4range(10, 20) * int4range(15, 25); -- 范围是否为空 SELECT isempty(numrange(1, 5)); ``` 范围类型的操作数和函数的完整列表见[Table 9-44](#calibre_link-1983) 及[Table 9-45](#calibre_link-1984)。 ## 8.17.3\. 包含及不包含边界每个非空范围有两个边界，下边界和上边界。这两个值之间的所有点都包含在范围内。包含边界意味着边界点本身包含在范围内，而不包含边界意味着边界点不包含在范围内。在一个文本格式的范围里，包含下边界由"`[`"代表，而不包含下边界由 "`(`"代表。同样，一个包含上边界由 "`]`"代表，而不包含上边界由"`)`" 代表。 (更多细节见[Section 8.17.5](#calibre_link-1985)）函数`lower_inc`和`upper_inc`分别检测一个范围值的上下边界是否包含。 ## 8.17.4\. 无限（无边界）范围一个范围的下边界可以被省略，意味着小于上边界的所有点都包含在范围内。同样，如果范围的上边界被省略，那么所有大于下边界的点都包含在范围内。如果上下边界都被省略，那么所有元素类型的值都被认为在范围内。相当于分别认为下边界是"负无穷大"，或者上边界是"正无穷大"。但是注意这些无穷大值绝不是范围元素类型的值，而且绝不是范围的一部分。（所以没有包含无穷边界之类的东西 — 如果你尝试写一个，它会被自动转换成一个不包含边界。）某些元素类型还有一个"无限"的概念，但是只要涉及到范围类型机制它就只是另一个值。例如，在timestamp范围里，`[today,]`意味着和`[today,)`是相同的东西。但是 `[today,infinity]`意味着与`[today,infinity)` 不同的东西 — 后者不包含特殊的`timestamp`值`infinity`。函数`lower_inf`和`upper_inf`分别检测一个范围的无限下边界和上边界。 ## 8.17.5\. 范围输入/输出范围值的输入必须遵循下面的格式： ``` (_下边界_,_上边界_) (_下边界_,_上边界_] [_下边界_,_上边界_) [_下边界_,_上边界_] 空 ``` 如前所述，圆括号或者方括号显示下边界和上边界是不包含的还是包含的。注意最后的格式是 `空`，代表着一个空的范围（一个不含有值的范围）。 `_下边界_`可以是子类型有效输入的一个字符串，或者是空以显示没有下边界。同样，`_上边界_`可以是子类型有效输入的一个字符串，或者是空以显示没有上边界。每个边界值可以用`"`（双引号）字符引用。如果边界值包含圆括号，方括号，逗号，双引号，或者反斜杠，这就很有必要。因为不这样的话，这些字符会被当成范围语法的一部分。要想把双引号或反斜杠放入一个引用的边界值，就在它前面加一个反斜杠。（另外，加了双引号的边界值内的两个连续的双引号用来表示一个双引号字符，类似于SQL文字串内的单引号规则。）或者，你可以避免用引号，使用反斜杠转义来保护所有数据字符不被认为是范围语法。而且，要写入一个空字符串边界值，用`""` 。这是因为什么也不写入意味着无限边界。在范围值前后可以有空格，但是在圆括号和方括号之间的任何空格都被认为是上边界或下边界的一部分。（重要还是不重要取决于元素类型。） > **Note:** 这些规则很类似于在复合类型常量中写入字段值。更多注释见[Section 8.16.5](#calibre_link-1986)。例子： ``` -- 包括3，不包括7，并且包括二者之间的所有点 SELECT '[3,7)'::int4range; -- 不包括3和7，但是包括二者之间所有点 SELECT '(3,7)'::int4range; -- 只包括单一值4 SELECT '[4,4]'::int4range; -- 不包括点（被标准化为‘空’） SELECT '[4,4)'::int4range; ``` ## 8.17.6\. 构造范围每个范围有一个与范围类型同名的构造函数。使用构造函数往往比写入一个范围文本常量更便利，因为它避免了额外引用边界值的需要。构造函数接受两到三个参数。两参数方式构造一个标准格式的范围（包含下边界，不包含上边界），而三参数方式用第三个参数指定边界来构造范围。第三个参数必须是下面的字符串之一 "`()`", "`(]`", "`[)`", 或 "`[]`". 例如： ``` -- 完整方式为：下边界，上边界，和指示包含还是不包含边界的文本参数 SELECT numrange(1.0, 14.0, '(]'); -- 如果第三个参数省略，使用‘[)’。 SELECT numrange(1.0, 14.0); -- 尽管在这里指定了‘(]’，然而该值会被转换成标准格式。这是由于int8range是一个离散范围类型（见下面）。 SELECT int8range(1, 14, '(]'); -- 使用NULL作任一边界会导致范围在那一边没有边界。 SELECT numrange(NULL, 2.2); ``` ## 8.17.7\. 离散范围类型离散范围的元素类型有一个完善定义的"阶梯"，例如`integer`或者 `date`。当在这些类型里两个元素中间没有有效值时，它们可被称为是邻近的。与之形成对比的是连续范围，总是（或者几乎总是）可能在两个给定值之间找到其它元素。例如， `numeric`类型的范围是连续的，和`timestamp`一样。（即使`timestamp`精度有限，理论上可以被当做是离散的，但是既然不关心阶梯大小最好还是把它当做是连续的。) 考虑一个离散范围的另一种方式是每一个元素值都清晰知道它的"下一个"或者"上一个" 值。知道了这个，通过选择下一个或者上一个元素值而不是开始给定的值，就可能在一个范围边界的包含和不包含表达之间进行转换。例如，在一个整数范围类型里`[4,8]`和`(3,9)` 提供相同的值集合；但是数值范围不是这样。离散范围应当有一个_标准化_函数，该函数知道元素类型想要的阶梯大小。这个标准化函数负责把该范围类型的等效值转换成同一表达方式，尤其是包含或不包含边界。如果不指定一个标准化函数，不同格式的范围会被认为是不相等的，即使它们实际上可能代表相同的值集合。内嵌范围类型`int4range`，`int8range`，和`daterange`都使用包括下边界不包含上边界的标准格式；即，`[)`。然而用户定义的范围类型可以使用其他规则。 ## 8.17.8\. 定义新的范围类型用户可以定义他们自己的范围类型。这么做通常是为了使用内嵌范围类型所不提供的子类型范围。例如，定义一个新的范围子类型`float8`: ``` CREATE TYPE floatrange AS RANGE ( subtype = float8, subtype_diff = float8mi ); SELECT '[1.234, 5.678]'::floatrange; ``` 因为`float8`没有有意义的"阶梯"，我们在此例中不定义一个标准化函数。如果子类型被认为含有离散的而不是连续的值，命令`CREATE TYPE`应当指定一个 `canonical`(标准化)函数。标准化函数使用一个输入范围值，并且必须返回一个可能含有不同边界和格式的相应的范围值。代表相同值集合的两个范围的输出必须相同，比如整数范围`[1, 7]`和`[1,8)`。你选择哪个表达方式作为标准没有关系，只要两个内容相当但格式不同的值总是映射到相同格式的相同值。除了调整包含/不包含边界格式以外，一旦想要的阶梯大小大于子类型能够储存的范围，标准化函数可能取整边界值。例如，`timestamp`范围类型可以被定义为以一个小时作为阶梯值。这种情况下标准化函数需要把不是一小时的整数倍的值化成一小时的整数倍，或者可能抛出一个错误。定义你自己的范围类型也允许你指定使用一个不同的子类型B-tree操作符类或排序规则，以便改变排序次序来决定哪些值落入一个给定的范围。此外，任何打算要使用GiST或SP-GiST索引的范围类型应当定义一个子类型差异，或者 `subtype_diff`函数。（没有`subtype_diff`索引仍然可以起作用，但是比起提供差异函数时可能相当低效。）子类型差异函数采用子类型的两个输入值，并返回它们之间表示为 `float8`值的差异（就是说，`_X_`减去`_Y_`）。在我们上面的例子中，可以使用常规`float8`减操作符调用的函数；但是对其它子类型，类型转换似乎是必要的。关于怎样将差异表示为数字的某些创新想法可能也是必要的。 `subtype_diff`函数应当尽最大可能与所选操作符类和排序规则表明的排序次序相一致；即，每当根据排序次序第一个参数大于第二个参数时，结果应当是正数。更多创建范围类型的信息见[CREATE TYPE](#calibre_link-100)。 ## 8.17.9\. 索引可以对范围类型的表列创建GiST和SP-GiST索引。例如，要创建一个GiST索引： ``` CREATE INDEX reservation_idx ON reservation USING gist (during); ``` 一个GiST或者SP-GiST索引可以加速包含这些范围操作符的查询： `=`, `&&`, `<@`, `@>`, `<<`, `>>`, `-|-`, `&<`, and `&>` （更多信息见[Table 9-44](#calibre_link-1983)）。此外，对范围类型的表列可以创建B-tree和哈希索引。对这些索引类型，基本上唯一可用的范围操作是等于。用相应的`<`和`>`操作符可以为范围索引定义一个B-tree排序次序，但是这个次序相当武断，在现实世界中通常没有用。范围类型的B-tree和哈希支持主要是用于查询内部的排序和哈希操作，而不是用于实际索引的创建。 ## 8.17.10\. 范围上的约束当`UNIQUE`是一个对标量值的自然约束时，对范围类型通常是不合适的。反而不包含约束往往更合适（见[CREATE TABLE ... CONSTRAINT ... EXCLUDE](#calibre_link-1064)）。不包含约束允许对一个范围类型指定约束，比如 "非重叠"。例如： ``` 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`](#calibre_link-372)扩展对简单标量数据类型定义不包含约束。简单标量数据类型可以和范围不包含结合来获得最大的灵活性。例如，在`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 ```