范围类型

范围类型是一种数据类型，它代表一种元素类型 (即范围类型的 子类型 ) 的一个范围. 举例说, timestamp 的范围类型可以用来表示一个会议室预定的时间范围. 在这个例子中，数据类型是 tsrange ( “timestamp range” 的缩写), 这里 timestamp 是子数据类型. 子数据类型必须有一个整体顺序，因此很好确定一个元素值是否在范围值域内，范围值域前，还是范围值域后面.

范围类型表示单个范围内的许多元素，所以非常有用, 因为一些概念，像重叠范围，能够用范围类型清晰表达. 为调度目的使用时间和日期是一个更清楚的例子; 对于价格范围, 仪器的测量范围, 等等, 也很有用.

父主题： Greenplum 数据库数据类型

内置范围类型

Greenplum 数据库带有以下内置范围类型:

  • int4range — integer 范围

  • int8range — bigint 范围

  • numrange — numeric 范围

  • tsrange — timestamp without time zone 范围

  • tstzrange — timestamp with time zone 范围

  • daterange — date 范围

除此之外, 你也可以定义你自己的范围类型; 参见 CREATE TYPE 了解更多信息.

举例

  1. CREATE TABLE reservation (room int, during tsrange);
  2. INSERT INTO reservation VALUES
  3.     (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');
  5. -- 包含
  6. SELECT int4range(10, 20) @> 3;
  8. -- 重叠
  9. SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);
  11. -- 提取上边界
  12. SELECT upper(int8range(15, 25));
  14. -- 计算交集
  15. SELECT int4range(10, 20) * int4range(15, 25);
  17. -- 是否为空?
  18. SELECT isempty(numrange(1, 5));

参见 范围函数和运算符 查看范围类型的操作符和函数完整列表.

包含边界与非包含边界

每一个非空范围有两个边界, 下边界和上边界. 所有在这两个边界之间的点都包含在范围内. 包含边界意味着边界点本身也包括在范围内, 而非包含边界意思是边界点不包括在范围内.

对于一个范围的文本格式, 包含下边界用 [ 表示, 而非包含下边界用 ( 表示. 同样, 包含上边界用 ] 表示, 而非包含上边界用 ) 表示. (参见 范围函数和运算符 了解更多信息.)

函数 upper_inc 和 lower_inc 分别用来测试一个范围值的上下边界包含关系.

无限 (无边界) 范围

范围的下边界可以忽略, 意味着所有小于上边界的点都包含在范围内. 同样地, 如果范围的上边界被忽略, 那么所有大于下边界的点都包含在范围内. 如果上下边界都忽略了, 那么这个元素类型的所有值都被认为包含在范围内.

可以等价地分别认为, 这个时候下边界是 “负无穷”, 或者上边界是 “正无穷”. 但是请注意, 这些无穷值不是一个范围的元素类型的值, 也不是范围的一部分. (所有无穷边界不存在包含问题 — 如果你这样写的话, 会自动转换为非包含边界.)

有些元素类型也有 “infinity” 的表示, 但那仅仅是一个值, 与范围边界无关. 举例说, 在时间戳范围中, [today,] 与 [today,) 含义相同. 但是 [today,infinity] 与 [today,infinity) 含义就不同了 — 后者排除了特殊的 时间戳 值 infinity.

函数 lower_inf 和 upper_inf 可以分别检测一个范围的无限上边界和下边界.

范围类型输入/输出

范围类型的输入必须遵循下面的模式:

  1. ( lower-bound, upper-bound )
  2. ( lower-bound, upper-bound ]
  3. [ lower-bound, upper-bound )
  4. [ lower-bound, upper-bound ]
  5. empty

和前面说的一样, 方括号或圆括号表示是否上下边界是包含, 非包含关系. 注意最后一种模式是 empty, 表示一个空范围 (一个不包含任何点的范围).

lower-bound 可以是一个子类型的有效字符串, 或者用 empty 表示没有下边界. 同样地, upper-bound 可以是一个子类型的有效字符串, 或者用 empty 表示没有上边界.

每一个边界值都可以是用 “ (双引号) 引起来的字符串. 如果边界值包含圆括号, 方括号, 逗号, 双引号, 反斜杠的时候, 则必须引起来, 因为这些字符可能是范围语法的一部分. 如果引起来的边界值有双引号或者反斜杠, 需要在前面插入额外的反斜杠进行转义. (在双引号引起来的边界值中两个双引号也用来表示一个双引号字符, 类似于 SQL 字面值中的单引号规则.) 当然, 你也可以避免引号, 只用反斜杠把所有会被认为是范围语法一部分的字符进行转义. 另外, 边界值也可以写成空字符串 “”, 因为啥也不写意味着无限边界.

在范围值的前后, 允许出现空格, 但是任何在圆括号或方括号之间的空格会被认为是上边界或下边界的一部分. (与元素类型有关, 或许有或许没意义.)

例如:

  1. -- 包含 3, 不包含 7, 并且包含期间的所有点
  2. SELECT '[3,7)'::int4range;
  4. -- 不包含 3 和 7, 单包含之间的所有点
  5. SELECT '(3,7)'::int4range;
  7. -- 只包含单个点 4
  8. SELECT '[4,4]'::int4range;
  10. -- 不包含任何点 (会被规范化为 'empty')
  11. SELECT '[4,4)'::int4range;

构造范围

每个范围类型有一个与范围类型同名的构造函数. 由于避免了边界值的额外引号(和转义), 通常使用构造函数比写一个范围字面常量更方便. 构造函数接受二或三个参数. 两个参数形式构造一个标准格式的范围 (包含下边界, 不包含上边界), 而三个参数格式构造一个通过第三个参数指定的边界(包含关系). 第三个参数必须是下面的字符串之一: (), (], [), 或者 []. 举例如下:

  1. -- 全格式是: 下边界, 上边界, 以及文本参数指示
  2. -- 包含/非包含边界.
  3. SELECT numrange(1.0, 14.0, '(]');
  5. -- 如果第三个参数忽略, 默认是 '[)'.
  6. SELECT numrange(1.0, 14.0);
  8. -- 虽然这里指定 '(]', 显示值会被转换成经典格式
  9. -- 因为 int8range 是一个离散范围类型 (见下).
  10. SELECT int8range(1, 14, '(]');
  12. -- 对某一边界使用 NULL 导致这一边无边界.
  13. SELECT numrange(NULL, 2.2);

离散范围类型

离散范围类型的元素类型有一个明确定义的 “步长”, 例如 integer 或 date. 在这些类型中, 当在两个元素间没有合法的值时, 我们就可以说这两个元素是相邻的. 这个与连续范围相对, 在连续范围中, 两个元素之间总是可以找到其它元素值. 例如, numeric 类型的范围就是连续的, timestamp 类型的范围也是连续的. (即使 timestamp 有有限的精度, 理论上可以认为它是离散的, 但由于步长值通常不重要, 所以最好认为它是连续的.)

另一个理解离散范围类型的方法是, 对于每一个元素值, 它有清晰的 “后一个” 和 “前一个” 值. 了解了这些, 就可以通过选择后一个或前一个元素值, 取代原始元素值, 来对范围边界的包含和非包含关系进行转换. 举例说, 一个整数范围类型 [4,8] 和 (3,9) 表示的是同样值的集合; 但对于一个 numeric 类型的范围就没有这样的关系.

一个离散范围类型应该有一个 canonicalization 函数来感知元素类型的步长. canonicalization 函数用于把等价范围类型值转换为同一表示, 特别是一致的包含或非包含边界. 如果没有指定 canonicalization 函数, 那么不同格式总是当成不相等对待, 即使他们实际上表示同样的值域.

内置范围类型 int4range, int8range, 和 daterange 全部使用一个包含下边界, 不包含上边界的一个经典格式; 即 [). 然而, 用户定义的范围类型可以使用其他转换形式.

定义新的范围类型

用户可以定义他们自己的范围类型. 这样做的通常原因是要使用的子类型的范围没有内置提供. 例如, 定义子类型为 float8 的新的范围类型:

  1. CREATE TYPE floatrange AS RANGE (
  2.     subtype = float8,
  3.     subtype_diff = float8mi
  4. );
  6. SELECT '[1.234, 5.678]'::floatrange;

因为 float8 没有 “步长”, 这个例子中我们不能定义一个 canonicalization 函数给它.

定义你自己的范围类型也允许你指定一个不同子类型 B-树 操作类或校对方式(collation)来使用, 可以改变排序规则, 以决定哪些值会落在给定范围之内.

如果子类型被认为是离散值而不是连续值, CREATE TYPE 命令应该指定一个 canonicalization 函数. canonicalization 函数接受一个范围值, 返回一个具有不同边界和格式的等价范围值. 对于表示同一值集的两个范围, canonicalization 函数输出一致., 例如整数范围 [1, 7] 和 [1, 8), 一定是相同的. 不论你选择哪一个作为标准输出, 只要不同格式的等价值总是映射到同样格式的相同值(就可以). 除了调整包含/非包含边界格式, canonicalization 函数还可以圆整(四舍五入)边界值, 例如设定的步长比子类型存储分辨能力宽(就需要这个功能). 例如, 一个 timestamp 范围类型, 可以定义为步长为 1 小时, 这种情况下, canonicalization 函数需要对不是整小时的边界进行圆整, 当然也可以抛出一个异常.

另外, 与 GiST 或 SP-GiST 索引一起使用的范围类型应该定义一个子类型差异函数, 即 subtype_diff, 函数. (没有 subtype_diff 函数索引也能工作, 但可能被认为没有提供这个函数时高效.) 子类型差异函数接受两个子类型输入值, 同时返回他们之间的差 (即, X 减去 Y ) 用 float8 值表示. 在我们上面的例子中, 函数 float8mi 隐含有常规 float8 相减操作来用; 但对于任何其他子类型, 某种类型的转换是必要的. 关于如何表示数值之间差异的一些创造性想法也很需要. 为了最大限度扩展这种可能, subtype_diff 函数应该与选定操作类和校对函数一致; 也就是说, 根据排序顺序, 当第一个参数比第二个大时, 这个函数应该范围正数.

一个关于 subtype_diff 函数不那么简单的例子是:

  1. CREATE FUNCTION time_subtype_diff(x time, y time) RETURNS float8 AS
  2. 'SELECT EXTRACT(EPOCH FROM (x - y))' LANGUAGE sql STRICT IMMUTABLE;
  4. CREATE TYPE timerange AS RANGE (
  5.     subtype = time,
  6.     subtype_diff = time_subtype_diff
  7. );
  9. SELECT '[11:10, 23:00]'::timerange;

参见 CREATE TYPE 了解更多关于创建范围类型的详细信息.

索引

GiST 和 SP-GiST 索引可以在范围类型列上创建. 例如, 创建一个 GiST 索引:

  1. CREATE INDEX reservation_idx ON reservation USING GIST (during);

一个 GiST 或者 SP-GiST 索引能够加速包含以下操作的范围运算: \=, &&, <@, @>, <<, >>, -|-, &<, 以及 &> (参见 范围函数和运算符 了解更多信息).

另外, B-树 和 hash 索引也可以创建在范围类型的表列上. 对于这些索引类型, 基本上只对相等范围操作有用. 也有一个范围值的 B-树 排序定义, 具有相应的 < 和 > 操作, 但是排序方法相当简单粗暴, 现实生活中并不常用. 范围类型的 B-树 和 hash 支持主要允许排序和查询内部建立 hash, 而不是为了创建实际的索引.