likes
comments
collection
share

PostgreSQL技术问答15 - Range

作者站长头像
站长
· 阅读数 55

本文是《PostgreSQL技术问答》系列文章中的一篇。关于这个系列的由来，可以参阅开篇文章：

文章的编号只是一个标识，在系列中没有明确的逻辑顺序和意义。读者进行阅读时，不用太关注这个方面。

本文主要讨论的内容是Range，就是数据范围。坦率的讲，虽然笔者知道在PostgreSQl中，有这么一种数据类型和技术，但实际上从未在工作中接触或者应用过。所以本文的内容可能有不成熟或者不准确的地方，主要作为一个学术上的讨论和知识扩展。欢迎读者指出本文的不足之处，笔者本人也希望在进一步了解和熟悉后，能够像位运算一样找到它的合适的应用场景。

什么是Range

在PG技术文档中，Range有专门的章节。可以看到，这个技术在很早就已经存在于PG中了。

www.postgresql.org/docs/9.2/ra…

Range，即范围。在PG数据库中，是一种内置的数据类型，它可以用于表达某种类型(范围的子类型)的元素的值的范围。Rang支持的子类型包括：

  • int4range — 整型
  • int8range — bigint，长整型
  • numrange — 数值型
  • tsrange — 时间戳类型，不带时区
  • tstzrange — 时间戳类型，带时区
  • daterange — 日期型

显然，Range能够基于某种数值类型，来表达一个数值范围。子类型必须具有总序，才能确定元素值是否在值范围内、之前或之后。但不同的数值类型，可以是离散(整型)，也可以是不离散(数值、时间)的。PG提出并使用Range的作用在于，可以用一个范围值来表示多个元素值，并且可以清晰地表达重叠范围、合并范围等概念。典型的使用场景包括使用时间和日期范围进行排期，或者表示价格范围、仪器测量范围也是很常用的方式。

要注意Range和Array的区别。Range在内部的值是连续的，而且是有序的，它的边界也是它的重要属性；Array可以任意定义组成的元素，而且它的边界也没有实际意义。

后面我们会从一些示例入手，讨论一下Range的使用方式和场景，从中我们将会感觉到，在一些特定的应用场景中，使用Range是非常简洁直观的。

举例说明？

下面有一些示例，让我们能够更直观的理解Range的概念和在PG中应用的方式。

// 会议室预留
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;
with D(rg) as (values (int4range(10,20))) select rg @>3 c3, rg @>10 c10, rg @>20 c20 from D;

c3 | c10 | c20 
----+-----+-----
 f  | t   | f
(1 row)


-- 两个范围是否重叠和重叠范围
SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0) r0 
int4range(10, 20) * int4range(15, 25) r1, 
int4range(10, 12) * int4range(15, 25) r2;

r0 |   r1    |  r2   
----+---------+-------
 t  | [15,20) | empty
(1 row)


 
-- 基于上下界生成范围
SELECT upper(int8range(15, 25)), lower(int8range(10, 45));

-- 判断范围是否为空?
SELECT isempty(numrange(1, 5));

-- 声明和类型转换
SELECT '[3,7)'::int4range;

从上面的一些示例，我们可以看到Range的一些使用方式和场景，这里简单总结一下：

  • 可以使用int4range、numrange等函数来构造一个range
  • 也可以使用字符串格式，来声明一个范围
  • 默认创建的范围取值为： 下边界 <= 范围 < 上边界
  • 可以使用 @> 运算符，检查值是否在范围内
  • 可以使用 isempty函数检查范围内容是否为空，特别如计算出来的范围
  • 可以使用 & 运算符，检查两个范围是否重叠
  • 可以使用 * 运算符，计算两个范围的重叠内容

Range有那些主要操作

如果认真研究PG的Range数据类型和功能实现，会发现其设计和实现的是相对完善的。我们所欠缺的，就是如何将这些特性，应用到我们的业务应用开发当中。在PG的技术文档有详细的内容：

www.postgresql.org/docs/9.2/fu…

Range相关操作，从形式上而言，主要有使用运算符和使用函数两种方式。它们几乎包括了我们能够想象到的范围运算和操作。通过这些内容，我们也可以研究和学习关于范围进行操作和计算的一般范式。

运算符

关于Range计算的运算符包括：

  • "=、<>" 两个范围是否相等或者不等
  • "<、<=、>、>=" 判断范围是否大于或者小于，它是通过比较范围边界来进行计算的
  • "@> 、 <@" 一个范围是否包含(被包含)另一个范围或者元素
  • "&&" 判断两个范围是否有交集
  • "<<、>>" 一个范围是否严格的在另一个范围的左边(右边)，或者完全小于或者大于
  • " -|- " 两个范围是否临近(adjacent)，即一个范围的上边界是另一个的下边界，并且至少有一个是包含的边界
  • "+、-、*" 两个范围的并集、差集或者交集

关于两个范围的交叉计算，笔者简单的理解如下，要注意是否包括边界值，使用时最好进行严格的测试并且充分理解技术文档中的描述：

  • 并集： 即两个相交的范围构成的新范围，下边界是两种较小的下边界，上边界是两者较大的上边界
  • 差集： 两个相交范围的差范围，下上边界就是两者的下边界
  • 交集： 两个相交范围的交范围，下边界是上范围的下边界，上边界是下范围的上边界

函数

主要包括：

  • lower/upper: 获取范围的低边界值和高边界值。
  • isempty: 判断范围是否为空，通常用于检测范围计算的结果
  • lower_inc/upper_inc: 范围是否包括上下边界值
  • lower_inf/upper_inf: 上下范围边界是否无限

Range如何表达和区分上下边界包含

秉承PG系统简洁优雅的传统，Range简单的使用 [] 和 () 来表示是否包括上下边界。如：

  • [下,上) : 下边界 <= 范围 < 上边界
  • [下,上] : 下边界 <= 范围 <= 上边界
  • (下,上] : 下边界 < 范围 <= 上边界
  • (下,上) : 下边界 < 范围 < 上边界

默认使用int4range等方法创建的范围是 [下边界,上边界)，即 下边界 <= 范围 < 上边界

可以在创建范围时，使用描述符号，来指定是否包含或者不包含上下边界。例如 int8range(1, 14, '(]')， 这个范围就不包含下边界，但包含上边界，和默认情况不同。

如何理解Range的无限边界

Range可以被定义为无限的边界，被称为Infinite(无限)或者Unbounded(无边界)。可以在定义时，不指定范围值(或者使用null)，就创建了一个无限的范围。无限范围可以在一个方向，也可以在两个方向， 比如'(,)'::daterange，就是一个两边都无限的日期范围。

可以给Range做索引吗？

可以，一般使用gist类型的索引，一般范式如下：

CREATE INDEX reservation_idx ON reservation USING gist (during);

Rang可以进行约束检查吗？

是可以的。但不是通常意义上Unique的唯一或者约束检查，而是通过Exclude约束检查方式。下面是一个检查时间范围冲突的约束示例：


// 创建一个 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扩展，它可以定义一般标量数据类型结合排除约束，从而提供一种非常灵活的约束检查机制。下面的示例代码方便我们进行理解：


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

这里面的要点在于：

  • 声明引入bree_gist扩展，来处理约束
  • 定义一个使用gist的排除约束，约束检查包括两个字段
  • 对于 room字段(简单标量类型) 执行 "=" 检查
  • 对于 during字段，这是范围数据类型，执行 && 重叠判断检查

可以自定义范围类型吗？

是可以的，比如下面的代码可以自定义一种浮点类型的范围：

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

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

小结

在本文中，笔者探讨了一个比较冷门，但其实非常有特点，在某些场景下也非常有用的特性：Range(范围)。简单解释了范围的基本概念，常用的用法和操作，以及一些可能在应用中涉及到的问题。

转载自:https://juejin.cn/post/7373162529817985043
评论
请登录