作者简介

JONATHAN S. KATZ

译者简介

张文升，PostgreSQL中文社区主席。

我可以滔滔不绝的谈论PostgreSQL对应⽤程序开发和操作的好处。但有两个我⼀直很关⼼的话题:SCRAM(你 需要更新密码才能使⽤SCRAM)和范围类型。

⾃从range类型在PostgreSQL 9.2中发布以来，我⼀直对它们感到很兴奋。在加⼊Crunchy Data之前，我在 ⽣产中⼤量部署了它们，以帮助管理⼀个成熟的⽇程安排和空间预订应⽤程序。在性能⽅⾯，范围类型提供 了⼀种快速的⽅式来执⾏对时态数据集的复杂查找!

尽管范围类型具有强⼤的功能和灵活性，但有⼀个不幸的限制:处理不重叠(或不连续)的范围。不要误会我的 意思，它让我有机会编写⼀些⾮常⾼级的递归SQL，并构建⼀些很酷的实时数据管理系统。然⽽，这是以便 利性和可维护性为代价的。

距离⾸次引⼊范围数据类型已经将近⼗年了，PostgreSQL 14现在增加了编写⼀些“乏味的SQL”的能⼒，以进 ⼀步释放处理范围数据的能⼒。满⾜“多范围”数据类型。

PostgreSQL 14: Déjà Vu All Over Again 感觉似曾相识：

PostgreSQL 9.2 引⼊了 JSON 和范围类型。PostgreSQL 14 添加了更好的 JSON 和多范围类型。回想⼀下 Postgres 有⼏个标准的范围类型：

int4range, int8range: ranges of int and bigint types.
numrange: ranges of numeric types.
tstzrange, daterange, tsrange, : ranges of timestamptz, date, and time
types.

记住，创建range类型就像这样简单:

SELECT daterange(CURRENT_DATE, CURRENT_DATE + 1);

Postgres 14现在增加了对多范围类型的⽀持。这些数据类型⽀持将⾮连续范围分组在⼀起。下⾯是⼀个简单 的例⼦

SELECT datemultirange(
 daterange(CURRENT_DATE, CURRENT_DATE + 2),
 daterange(CURRENT_DATE + 5, CURRENT_DATE + 8)
 );
 datemultirange
---------------------------------------------------
{[2021-06-08,2021-06-10),[2021-06-13,2021-06-16)}
(1 row)

很酷的是，现在我可以在这些multirange类型中查询，并确定是否有⼀个特定的值，或者是否有⼀个重叠的 范围:

SELECT CURRENT_DATE + 1 <@ datemultirange(
 daterange(CURRENT_DATE, CURRENT_DATE + 2),
 daterange(CURRENT_DATE + 5, CURRENT_DATE + 8)
);
 ?column?
----------
 t
(1 row)

SELECT CURRENT_DATE + 3 <@ datemultirange(
 daterange(CURRENT_DATE, CURRENT_DATE + 2),
 daterange(CURRENT_DATE + 5, CURRENT_DATE + 8)
);
 ?column?
----------
 f
(1 row)

让我们看看重叠的⾮连续部分的范围!

SELECT daterange(CURRENT_DATE + 1, CURRENT_DATE + 5) && datemultirange(
 daterange(CURRENT_DATE, CURRENT_DATE + 2),
 daterange(CURRENT_DATE + 5, CURRENT_DATE + 8)
);
 ?column?
----------
 t
(1 row)

以及不重叠⾮相邻部分的范围:

SELECT daterange(CURRENT_DATE + 3, CURRENT_DATE + 4) && datemultirange(
 daterange(CURRENT_DATE, CURRENT_DATE + 2),
 daterange(CURRENT_DATE + 5, CURRENT_DATE + 8)
);
 ?column?
----------
 f
(1 row)

Cool~!通过在范围类型名称的末尾添加multirange后缀，可以从现有的范围类型创建多个范围，例如:

int4multirange, int8multirange: ranges of int and bigint range types.
nummultirange: multirange of numeric range types.
tstzmultirange, datemultirange, tsmultirange, : multirange of timestamptz,
date, and time range types.

然⽽，这只是浅显的触及了多范围类型的功能。我认为最酷的部分是，它可以极⼤地简化⼀个过于复杂的递 归查询，只使⽤⼀个聚合:range_agg。

换句话说，让我们将200⾏SQL转换为3⾏。

⼀个真实世界的例⼦：可⽤性

让我们构建⼀个类似的例⼦，就像我在之前的博客⽂章中⽤范围类型搜索可⽤性⼀样。我们将把这个例⼦更 改为带有约会的天数:

CREATE TABLE appointments (
 id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
 appointment_dates daterange NOT NULL,
 EXCLUDE USING gist (appointment_dates WITH &&)
);

让我们创建⼀组类似于上⼀个例⼦的约会⽇期:

INSERT INTO appointments (appointment_dates)
VALUES
 (daterange('2021-06-02', '2021-06-02', '[]')),
 (daterange('2021-06-06', '2021-06-09', '[]')),
 (daterange('2021-06-11', '2021-06-12', '[]')),
 (daterange('2021-06-16', '2021-06-17', '[]')),
 (daterange('2021-06-25', '2021-06-27', '[]'));

换句话说，预约⽇期是：

June 2
June 6 - June 9
June 11 - June 12
June 16 - June 17
June 25 - June 27

现在，假设我们想要返回我们在6⽉份预约的所有⽇期。我们可以通过⼀个查询来完成:

SELECT appointment_dates
FROM appointments
WHERE appointment_dates && daterange('2021-06-01', '2021-06-30', '[]');
 appointment_dates
-------------------------
 [2021-06-02,2021-06-03)
 [2021-06-06,2021-06-10)
 [2021-06-11,2021-06-13)
 [2021-06-16,2021-06-18)
 [2021-06-25,2021-06-28)

如果我们想要将所有这些⽇期聚集到⼀个单⼀的多区间中，我们可以使⽤range_agg来实现；

SELECT range_agg(appointment_dates)
FROM appointments
WHERE appointment_dates && daterange('2021-06-01', '2021-06-30', '[]');
 range_agg
--------------------------------------------------------------------------
-------------------------------------------------
 {[2021-06-02,2021-06-03),[2021-06-06,2021-06-10),[2021-06-11,2021-06-13),
[2021-06-16,2021-06-18),[2021-06-25,2021-06-28)}

回想⼀下，前⾯的例⼦想要解决的问题是“在给定的⼀个⽉内，我可以在什么时间预约”。为了解决这个问 题，我们编写了⼀个相当复杂的递归查询。对于多范围类型，我们可以简化为:

SELECT datemultirange(daterange('2021-06-01', '2021-06-30', '[]')) -
 range_agg(appointment_dates) AS availability
FROM appointments
WHERE appointment_dates && daterange('2021-06-01', '2021-06-30', '[]');

availability
--------------------------------------------------------------------------
-------------------------------------------------------------------------
 {[2021-06-01,2021-06-02),[2021-06-03,2021-06-06),[2021-06-10,2021-06-11),
[2021-06-13,2021-06-16),[2021-06-18,2021-06-25),[2021-06-28,2021-07-01)}

Wow!这样就简单多了。虽然我展示的前⼀种⽅法也是有效的，但它更简单，更不容易出错，并具备更多优 点，这些优点包括:

更容易理解:这仅仅是3⾏简单的SQL，⽽不是⼀个复杂的递归查询。

更容易维护:使⽤更少的SQL，维护就更少，⽐如对模式进⾏更新。

更少的存储:由于查询的成本，前⾯的⽅法要求您“预计算”较⼤系统上的可⽤性。通过使⽤范围聚合 (range_agg)和多数据类型差异(-)操作符的效率，即使不需要预先计算可⽤性，也可以减少这种情况。

后续

Postgres 14现在处于beta阶段。我强烈建议您下载它，仔细研究⼀下，看看还有什么其他有趣的新特性。如果您已经在以不同的⽅式使⽤范围类型，我建议您尝试⼀下新的多范围类型:您可以在不牺牲性能的情况下 简化查询！

原文链接：https://blog.crunchydata.com/blog/better-range-types-in-postgres-14-turning-100-lines-of-sql-into-3

PostgreSQL中文社区欢迎广大技术人员投稿

投稿邮箱：press@postgres.cn