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

PG中文社区 /

mdi-home

首页

社区新闻

中文文档

加入ACE

mdi-home

首页

mdi-chat-processing

社区新闻

mdi-book-open-variant

中文文档

mdi-account-multiple-check

加入ACE

mdi-file-multiple-outline

相关资料

mdi-blank

mdi-exit-to-app

退出账号

Postgres 14 中更好的范围类型：将 100 ⾏ SQL 变成 3 ⾏

原作者：JONATHAN S. KATZ　　创作时间：2021-07-07 21:26:04+08　　

wangliyun 发布于2021-07-08 08:00:00　　

评论: 1　　

顶: 926　

踩: 914　

### **作者简介**

#### 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**

评论:1　　

顶: 926　

踩: 914　

请在登录后发表评论，否则无法保存。

1# __ xcvxcvsdf 回答于 2024-10-17 17:16:07+08

http://taiying.njtcbmw.cn/shminhang/ http://ouyu.hftcbmw.cn/ynkm/ http://fuyang.tjtcbmw.cn/dnzr/ http://huilong.sctcbmw.cn/hnjz/ http://ly.shtcxxw.cn/suihua/ https://jianxi.tiancebbs.cn/ http://ruanwen.xztcxxw.cn/ftq/ http://gx.lztcxxw.cn/bmqz/ http://nalei.zjtcbmw.cn/luwansh/ http://gx.lztcxxw.cn/jiangmen/ https://chaozhouzhoubian.tiancebbs.cn/ https://zcchengxi.tiancebbs.cn/ http://km.lstcxxw.cn/baishan/ http://fuyang.tjtcbmw.cn/jsha/ http://ruanwen.xztcxxw.cn/maoming/ http://bjtcxxw.cn/hbxg/ http://fuyang.tjtcbmw.cn/ezhou/