PostgreSQL中鲜为人知的特性

2006年，微软进行了一项客户调查，以了解用户在新版本的Office中想要哪些新功能。令他们惊讶的是，90%以上的用户要求的功能已经存在，只是他们不知道而已。为了解决 "可发现性 (discoverability) "的问题，他们想出了我们今天从微软Office产品中了解到的 "Ribbon UI"。

从某种意义上来说，Office并不是唯一有这种情况的。大多数人并不知道我们日常使用的工具中的所有功能，尤其是像PostgreSQL这样大而全的数据库。由于PostgreSQL 14在几周前刚刚发布，这是一个多么好的机会来阐明PostgreSQL中已经存在的一些鲜为人知的功能，但你可能不知道。

在本文中，我会介绍 PostgreSQL中的一些鲜为人知的特性。

文章内容

• • 获取 Upsert 中更新和插入的行数

• • 授予特定列的权限

• • 匹配多个模式

• • 获取序列当前值而无需使用nextval

• • 对多行SQL使用 \COPY

• • 防止设置自动生成键的值

• • 多种方式生成Pivot表

• • Dollar Quoting

• • 对数据库对象进行注释

• • 为每个数据库保存一个单独的历史文件

• • 保留字自动补全为大写

• • 休眠指定时间

• • 不使用子查询，获取分组中的第一行或最后一行

• • 无需扩展生成UUID

• • 生成可"复现"的随机数

• • 添加约束而无需立即校验

• • PostgreSQL中的同义词

• • 找到重叠的范围

获取Upsert中更新和插入的行数

INSERT ON CONFLICT，也称为 "merge"（Oracle）或者 upsert （UPDATE和INSERT的组合）是一个十分有用的命令，尤其对于ETL这样的操作。在INSERT语句中使用ON CONFLICT子句，你可以告诉数据库，当一个或多个关键列检测到冲突时该怎么做。

例如，这里有一个查询来同步employees表中的数据：

db=# WITH new_employees AS (
    SELECT * FROM (VALUES
        ('George', 'Sales',    'Manager',   1000),
        ('Jane',   'R&D',      'Developer', 1200)
    ) AS t(
         name,      department, role,       salary
    )
)
INSERT INTO employees (name, department, role, salary)
SELECT name, department, role, salary
FROM new_employees
ON CONFLICT (name) DO UPDATE SET
    department = EXCLUDED.department,
    role = EXCLUDED.role,
    salary = EXCLUDED.salary
RETURNING *;

  name  │ department │   role    │ salary
────────┼────────────┼───────────┼────────
 George │ Sales      │ Manager   │   1000
 Jane   │ R&D        │ Developer │   1200
INSERT 0 2

该查询将新员工的数据插入到了表中。如果试图添加一个姓名已经存在的员工，作为替代，该查询会转为更新该行。

你可以观察到上述命令的输出，INSERT 0 2，表示插入了两名员工信息。但是有多少员工信息插入了，又有多少进行了更新？输出内容并没有给我们任何线索。

当我在寻找一种方法来改进使用这种查询的某些 ETL 进程的日志记录时，我在Stack Overflow上偶然发现了一个关于这个问题的聪明的解决方案：

db=# WITH new_employees AS (
    SELECT * FROM (VALUES
        ('George', 'Sales',    'Manager',   1000),
        ('Jane',   'R&D',      'Developer', 1200)
    ) AS t(
         name,      department, role,       salary
    )
)
INSERT INTO employees (name, department, role, salary)
SELECT name, department, role, salary
FROM new_employees
ON CONFLICT (name) DO UPDATE SET
    department = EXCLUDED.department,
    role = EXCLUDED.role,
    salary = EXCLUDED.salary
RETURNING *, (xmax = 0) AS inserted;

  name  │ department │   role    │ salary │ inserted
────────┼────────────┼───────────┼────────┼──────────
 Jane   │ R&D        │ Developer │   1200 │ t
 George │ Sales      │ Manager   │   1000 │ f
INSERT 0 2

注意观察RETUNING子句的不同。它包括inserted的计算字段，该字段使用特殊列 xmax 来确认插入了多少行。根据命令所返回的数据，你可以观察到，"Jane"作为一行新数据被插入，而"George"由于在表中已经存在，因此该行被更新了。

xmax是一个特殊的系统列

The identity (transaction ID) of the deleting transaction, or zero for an undeleted row version.

在PostgreSQL中，当某行被更新，老版本被删除，xmax会存放删除该行的事务ID。而插入新行，没有老版本的行被删除，因此xmax是0。这个"技巧"十分聪明，正是使用了这个特性来区分插入和更新的行。

授予特定列的权限

假设有一个包含敏感信息的用户表，比如凭证、密码或 PII

db=# CREATE TABLE users (
    id INT,
    username VARCHAR(20),
    personal_id VARCHAR(10),
    password_hash VARCHAR(256)
);
CREATE TABLE

db=# INSERT INTO users VALUES (1, 'haki', '12222227', 'super-secret-hash');
INSERT 1 0

组织中的不同人员使用该表，比如分析员，访问数据并生成特定的报告。为了允许访问，需要在数据库中添加一个特殊的用户：

db=# CREATE USER analyst;
CREATE USER

db=# GRANT SELECT ON users TO analyst;
GRANT

analyst用户现在可以访问user表了：

db=# \connect db analyst
You are now connected to database "db" as user "analyst".

db=> SELECT * FROM users;
 id │ username │ personal_id │   password_hash
────┼──────────┼─────────────┼───────────────────
  1 │ haki     │ 12222227    │ super-secret-hash

正如之前所提及的，analyst访问用户数据来生成报告和进行分析，但他们不应该访问敏感信息或 PII。

为了提供对用户可以访问表中哪些数据的细粒度控制，PostgreSQL允许仅对表中的特定列授予权限：

db=# \connect db postgres
You are now connected to database "db" as user "postgres".

db=# REVOKE SELECT ON users FROM analyst;
REVOKE

db=# GRANT SELECT (id, username) ON users TO analyst;
GRANT

在回收了表上现有的查询权限之后，仅仅授予analyst用户id和usename列上的查询权限。现在，analyst用户再也不能访问这些列了：

db=# \connect db analyst
You are now connected to database "db" as user "analyst".

db=> SELECT * FROM users;
ERROR:  permission denied for table users

db=> SELECT id, username, personal_id FROM users;
ERROR:  permission denied for table users

db=> SELECT id, username FROM users;
 id │ username
────┼──────────
  1 │ haki

可以看到，当analyst用户试图访问任何受限的列，不管是显式或者隐式地使用*，都会得到一个"permission denied"的错误。

匹配多个模式

在SQL中使用模式匹配并不少见。比如如下查询可以找到拥有"gmail. com"电子邮件帐户的用户：

SELECT *
FROM users
WHERE email LIKE '%@gmail.com';

这个查询使用通配符 ''%' 来查找邮件以"@gmail. com" 结尾的用户。如果在同一个查询中，你还希望找到拥有"yahoo. com"电子邮件帐户的用户，那该怎么办？

SELECT *
FROM users
WHERE
    email LIKE '%@gmail.com'
    OR email LIKE '%@yahoo.com'

为了匹配这些模式中的任何一个，你可以构造一个 or 条件。然而在PostgreSQL中，有另外一种方式可以匹配多个模式：

SELECT *
FROM users
WHERE email SIMILAR TO '%@gmail.com|%@yahoo.com'

使用 SIMILAR TO 你可以匹配多个模式并且使查询更加简洁。

另外一种匹配多个模式的方式是使用正则表达式：

SELECT *
FROM users
WHERE email ~ '@gmail\.com$|@yahoo\.com$'

当使用正则表达式的时候，你需要更加小心。一个句号"." 会匹配任何东西，所以为了在gmail. com或yahoo. com中匹配句号 "." ，需要添加转义字符"\."。

当我在 twitter 上分享了这篇文章后，我收到了一些有趣的回答。来自 psycopg 官方账户的一条评论提出了另一种方法：

SELECT *
FROM users
WHERE email ~ ANY(ARRAY['@gmail\.com$', '@yahoo\.com$'])

该查询使用 ANY 运算符来匹配模式数组。如果一封电子邮件符合任何一种模式，那么这个条件就是 true 。这种方法在 Python 这样的宿主语言中更容易使用：

with connection.cursor() as cursor:
    cursor.execute('''
        SELECT *
        FROM users
        WHERE email ~ ANY(ARRAY%(patterns)s)
    ''' % {
        'patterns': [
            '@gmail\.com$',
            '@yahoo\.com$',
        ],
    })

不同于前一种使用 SIMILAR TO的方式，使用 ANY 你可以将模式列表绑定到某个变量。

获取序列当前值而无需使用nextval

如果你需要获得某个序列的当前值，你首先很可能会使用 currval

db=# SELECT currval('sale_id_seq');
ERROR:  currval of sequence "sale_id_seq" is not yet defined in this session

好比我一样，你可能也发现了 currval 只有在当前会话中定义或使用序列时才能正常工作。没有足够的理由去推进一个序列（nextval），这通常不是你想做的事情，因此这不是一个可接受的解决方案。

在PostgreSQL 10中添加了 pg_sequences 视图，以便于获取序列的信息

db=# SELECT * FROM pg_sequences WHERE sequencename = 'sale_id_seq';
─[ RECORD 1 ]─┬────────────
schemaname    │ public
sequencename  │ sale_id_seq
sequenceowner │ db
data_type     │ integer
start_value   │ 1
min_value     │ 1
max_value     │ 2147483647
increment_by  │ 1
cycle         │ f
cache_size    │ 1
last_value    │ 155

这个视图可以回答你的问题，但这并不是一个"鲜为人知的特性"，它仅仅是位于infomation schema下面的一张表而已。

另外一种获取某个序列的当前值是使用未曾记录的函数pg_sequence_last_value：

db=# SELECT pg_sequence_last_value('sale_id_seq');
 pg_sequence_last_value
────────────────────────
                   155

不清楚为何没有记录这个函数，但我在官方文档中并没有找到任何地方提及到它。如果你打算使用它，需要考虑到这一点。

当我在研究的时候我还发现另一个有趣的东西，你可以像查询表一样查询序列：

db=# SELECT * FROM sale_id_seq;

 last_value │ log_cnt │ is_called
────────────┼─────────┼───────────
        155 │      10 │ t

这确实让你想知道在 PostgreSQL 中还可以查询什么类型的对象，以及你会得到什么样的返回值。

值得注意的是，除了粗略地看一下序列，这个特征不应该用于其他任何用途。你不应该尝试基于此输出的值去更新 ID，而是应该使用 nextval。

对多行SQL使用 \COPY

如果经常使用 psql，那么你可能经常使用 \COPY 从数据库中导出数据。我就会。关于 \COPY 最让人头疼的事情便是它不支持多行查询：

db=# \COPY (
\copy: parse error at end of line

当你尝试给 \COPY 命令新增一行的时候，你就会得到这个错误。

为了解决这个限制，我首先想到的是使用视图：

db=# CREATE VIEW v_department_dbas AS
    SELECT department, count(*) AS employees
    FROM emp
    WHERE role = 'dba'
    GROUP BY department
    ORDER BY employees;
CREATE VIEW

db=# \COPY (SELECT * FROM v_department_dbas) TO department_dbas.csv WITH CSV HEADER;
COPY 5

db=# DROP VIEW v_department_dbas;
DROP VIEW;

这种方法是可行的，但是如果中间有什么东西出了问题，就会留下一些视图。我喜欢让我的模式保持简洁，所以我在寻找一种自动清理的方法。快速搜索一下就会得到temporary views：

db=# CREATE TEMPORARY VIEW v_department_dbas AS # ...
CREATE VIEW

db=# \COPY (SELECT * FROM v_department_dbas) TO department_dbas.csv WITH CSV HEADER;
COPY 5

使用临时视图后，我不再需要自己去清理，因为当会话终止时，临时视图会自动删除。

我使用了一阵子临时视图，直到我在 psql 文档中找到了这个：

db=# COPY (
    SELECT department, count(*) AS employees
    FROM emp
    WHERE role = 'dba'
    GROUP BY department
    ORDER BY employees
) TO STDOUT WITH CSV HEADER \g department_dbas.csv
COPY 5

很不错，是吧？让我们来分析一下：

• • 使用COPY替代\COPY：COPY 命令是在服务器中执行的服务器命令，而 \COPY 是具有相同接口的 psql 命令。因此，虽然 COPY 不支持多行查询，但 COPY 支持！

• • 将结果输出到标准输出STDOUT：使用 COPY 可以将结果写入到服务器上的目录，或者使用 TO STDOUT 将结果写入到标准输出。

• • 使用\g 将写入到STDOUT的内容写入到本地文件：最后，psql 提供了一个命令，将标准输出STDOUT的输出写入到文件中。

将这三个特性结合，就实现了我想要的。

COPY EXPERT

If you move a lot of data around, don't miss the fastest way to load data into PostgreSQL using Python.

防止设置自动生成键的值

如果你在 PostgreSQL 中使用自增主键，那么你可能还在使用 SERIAL 数据类型

CREATE TABLE sale (
    id SERIAL PRIMARY KEY,
    sold_at TIMESTAMPTZ,
    amount INT
);

在幕后，当新增行时 PostgreSQL 实际上创建了一个序列：

db=# INSERT INTO sale (sold_at, amount) VALUES (now(), 1000);
INSERT 0 1

db=# SELECT * FROM sale;
 id │           sold_at             │ amount
────┼───────────────────────────────┼────────
  1 │ 2021-09-25 10:06:56.646298+03 │   1000

SERIAL 数据类型对于 PostgreSQL 是唯一的，并且有一些已知的问题，所以从版本10开始，SERIAL类型不建议使用，而是使用身份列 identity columns

CREATE TABLE sale (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    sold_at TIMESTAMPTZ,
    amount INT
);

identity columns 和 SERIAL 工作方式十分类似

db=# INSERT INTO sale (sold_at, amount) VALUES (now(), 1000);
INSERT 0 1

db=# SELECT * FROM sale;
 id │           sold_at             │ amount
────┼───────────────────────────────┼────────
  1 │ 2021-09-25 10:11:57.771121+03 │   1000

但是考虑如下场景：

db=# INSERT INTO sale (id, sold_at, amount) VALUES (2, now(), 1000);
INSERT 0 1

db=# INSERT INTO sale (sold_at, amount) VALUES (now(), 1000);
ERROR:  duplicate key value violates unique constraint "sale_pkey"
DETAIL:  Key (id)=(2) already exists.

为什么会失败？

• • 第一个 INSERT 语句显式针对 id 列插入了2，因此序列并没有使用到

• • 第二个 INSERT 语句并未针对 id 列提供值，因此使用了序列。而序列的下一个值恰巧是2，所以由于违背了唯一约束，命令失败了

自增 id 很少需要手动设置，这样做可能会造成混乱。因此如何避免用户手动设置他们？

CREATE TABLE sale (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sold_at TIMESTAMPTZ,
    amount INT
);

不要使用 GENERATED BY DEFAULT，而是使用 GENERATED ALWAYS。为了搞清楚差异，再次尝试同样的场景：

db=# INSERT INTO sale (sold_at, amount) VALUES (now(), 1000);
INSERT 0 1

db=# INSERT INTO sale (id, sold_at, amount) VALUES (2, now(), 1000);
ERROR:  cannot insert into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

有什么变化？

• • 第一个 INSERT 命令并没有针对 id 列提供值并且插入成功了

• • 第二个 INSERT 命令尝试将 id 列设为2结果失败了！

在错误消息中，PostgreSQL 非常友好地提供了一个解决方案，当你确实想要显式地设置一个标识列的值时：

db=# INSERT INTO sale (id, sold_at, amount)
OVERRIDING SYSTEM VALUE VALUES (2, now(), 1000);

INSERT 0 1

通过在 INSERT 命令中添加 OVERRIDING SYSTEM VALUE，显式指示 PostgreSQL 以允许设置标识列的值。当然你仍然得处理一个可能的唯一约束冲突，但是不能再将其归咎于 PostgreSQL！

多种方式生成Pivot表

在我之前的一篇文章中，我演示了how to produce pivot tables using conditional aggregates。在写完这篇文章之后，我又找到了两种在 PostgreSQL 中生成 pivot tables 的方法。

假设你想要得到每个部门每个角色的员工人数：

db=# WITH employees AS (
    SELECT * FROM (VALUES
        ('Haki',    'R&D',      'Manager'),
        ('Dan',     'R&D',      'Developer'),
        ('Jax',     'R&D',      'Developer'),
        ('George',  'Sales',    'Manager'),
        ('Bill',    'Sales',    'Developer'),
        ('David',   'Sales',    'Developer')
    ) AS t(
        name,       department,  role
    )
)
SELECT role, department, count(*)
FROM employees
GROUP BY role, department;

   role    │ department │ count
───────────┼────────────┼───────
 Developer │ Sales      │     2
 Manager   │ Sales      │     1
 Manager   │ R&D        │     1
 Developer │ R&D        │     2

一个更好的看待这个问题的方式是将其视为一个pivot table。在 psql 中，可以使用 \crosstabview 命令将上次查询的结果转换为 pivot 表:

db=# \crosstabview

   role    │ Sales │ R&D
───────────┼───────┼─────
 Developer │     2 │   2
 Manager   │     1 │   1

这是魔法！默认情况下，命令会从前两列生成 pivot 表，但是你可以用参数来控制它：

db=# \crosstabview department role

 department │ Developer │ Manager
────────────┼───────────┼─────────
 Sales      │         2 │       1
 R&D        │         2 │       1

另一个稍微不那么神奇的制作 pivot 表的方式是使用内置的 tablefunc 扩展：

db=# CREATE EXTENSION tablefunc;
CREATE EXTENSION

db=# SELECT * FROM crosstab('
    SELECT role, department, count(*) AS employees
    FROM employees
    GROUP BY 1, 2
    ORDER BY role
', '
    SELECT DISTINCT department
    FROM employees
    ORDER BY 1
') AS t(role text, sales int, rnd int);

   role    │ sales │ rnd
───────────┼───────┼─────
 Developer │     2 │   2
 Manager   │     1 │   1

使用 crosstab 函数你可以生成一个 pivot 表。此方法的缺点是需要提前定义输出列。但是，其优点是 crosstab 函数会生成一个表，所以你可以将其用作后续处理的子查询。

Dollar Quoting

如果在数据库中存储文本字段，特别是整个段落，那么你可能对转义字符很熟悉。例如，要在文本中包含一个单引号，你需要使用另一个单引号来转义：

db=# SELECT 'John''s Pizza';
   ?column?
──────────────
 John's Pizza

当文本越来越大，包括反斜杠和新行之类的字符时，添加转义字符会变得相当烦人。为了解决这个问题，PostgreSQL 提供了另一种编写字符串常量的方法：

db=# SELECT $$a long
string with new lines
and 'single quotes'
and "double quotes

PostgreSQL doesn't mind ;)$$ AS text;
           text
───────────────────────────
 a long                   ↵
 string with new lines    ↵
 and 'single quotes'      ↵
 and "double quotes       ↵
                          ↵
 PostgreSQL doesn't mind ;)

注意字符串开头和结尾的美元符号 $$。$$之间的任何值都会被视为一个字符串。PostgreSQL 称之为 "Dollar Quoting" 。

但是假如你碰巧还需要在文本中使用 $$ 符号，你可以添加一个标签，这使得它更加有用。比如：

db=# SELECT $JSON${
    "name": "John's Pizza",
    "tagline": "Best value for your $$"
}$JSON$ AS json;

                  json
─────────────────────────────────────────
 {                                      ↵
     "name": "John's Pizza",            ↵
     "tagline": "Best value for your $$"↵
 }

我们选择将这一段代码块打上 $JSON$ 的标签，因此 "$$"符号作为一个整体输出了出来。

你也可以使用这一点快速生成包含特殊符号的 jsonb 对象：

db=# SELECT $JSON${
    "name": "John's Pizza",
    "tagline": "Best value for your $$"
}$JSON$::jsonb AS json;
                          json
─────────────────────────────────────────────────────────────
 {"name": "John's Pizza", "tagline": "Best value for your $$"}

该值现在是一个 jsonb 对象，你可以随心所欲地操作它。

对数据库对象进行注释

PostgreSQL 有一个很好的小特性，你可以在每个数据库对象上添加注释。例如，在表上添加注释：

db=# COMMENT ON TABLE sale IS 'Sales made in the system';
COMMENT

你可以在psql中观察这个注释 (也可以在其他的IDE中)：

db=# \dt+ sale
                                  List of relations
 Schema │ Name │ Type  │ Owner │ Persistence │    Size    │       Description
────────┼──────┼───────┼───────┼─────────────┼────────────┼──────────────────────────
 public │ sale │ table │ haki  │ permanent   │ 8192 bytes │ Sales made in the system

你也可以在表列上添加注释，并在使用扩展描述时查看它们：

db=# COMMENT ON COLUMN sale.sold_at IS 'When was the sale finalized';
COMMENT

db=# \d+ sale
  Column  │           Type           │         Description
──────────┼──────────────────────────┼─────────────────────────────
 id       │ integer                  │
 sold_at │ timestamp with time zone │ When was the sale finalized
 amount   │ integer 

你也可以把 COMMENT 命令和 dollar quoting 结合起来，包含更长更有意义的描述，例如，函数：

COMMENT ON FUNCTION generate_random_string IS $docstring$
Generate a random string at a given length from a list of possible characters.

Parameters:

    - length (int): length of the output string
    - characters (text): possible characters to choose from

Example:

    db=# SELECT generate_random_string(10);
     generate_random_string
    ────────────────────────
     o0QsrMYRvp

    db=# SELECT generate_random_string(3, 'AB');
     generate_random_string
    ────────────────────────
     ABB
$docstring$;

这是我过去使用的一个函数，用来说明中等大小的文本对性能的影响。现在我不再需要回到文章中去记住如何使用这个函数，因此我在注释中就有描述：

db=# \df+ generate_random_string
List of functions
────────────┬────────────────────────────────────────────────────────────────────────────────
Schema      │ public
Name        │ generate_random_string
/* ... */
Description │ Generate a random string at a given length from a list of possible characters.↵
            │                                                                               ↵
            │ Parameters:                                                                   ↵
            │                                                                               ↵
            │     - length (int): length of the output string                               ↵
            │     - characters (text): possible characters to choose from                   ↵
            │                                                                               ↵
            │ Example:                                                                      ↵
            │                                                                               ↵
            │     db=# SELECT generate_random_string(10);                                   ↵
            │      generate_random_string                                                   ↵
            │     ────────────────────────                                                  ↵
            │      o0QsrMYRvp                                                               ↵
            │                                                                               ↵
            │     db=# SELECT generate_random_string(3, 'AB');                              ↵
            │      generate_random_string                                                   ↵
            │     ────────────────────────                                                  ↵
            │      ABB                                                                      ↵
            │

为每个数据库保存一个单独的历史文件

如果使用 CLI 工具，那么你可能会经常使用搜索历史命令的功能。在Bash和psql中，反向搜索通常是使用 CTRL + R

如果除了使用终端之外，还要使用多个数据库，那么你可能会发现为每个数据库保存一个单独的历史文件非常有用：

db=# \set HISTFILE ~/.psql_history- :DBNAME

这样，您就更有可能找到与当前连接的数据库相关的匹配对象。你可以把这个写入到 ~/.psqlrc 文件里面以使它持久化。

保留字自动补全为大写

总是有很多争论 (和笑话!) 关于 SQL 中的关键字应该是大写还是小写。我认为我对这个问题的看法很清楚。

如果你和我一样，喜欢在 SQL 中使用大写关键字，那么 psql 中有一个选项可以自动补全大写关键字：

db=# selec <tab>
db=# select

db=# \set COMP_KEYWORD_CASE upper
db=# selec <tab>
db=# SELECT

将 comp_keyword_case 设置为 upper 后，当键入 TAB 进行补全时，会自动将关键字补全为大写。

休眠指定时间

延迟某个程序的执行对于诸如测试或限流之类的事情是非常有用的。为了延迟 PostgreSQL 中程序的执行，go-to 函数通常是 pg_sleep：

db=# \timing
Timing is on.

db=# SELECT pg_sleep(3);
 pg_sleep
──────────

(1 row)

Time: 3014.913 ms (00:03.015)

函数在指定的秒数内处于休眠状态。然而，当你需要睡眠超过几秒钟的时候，计算秒数可能会很烦人，例如：

db=# SELECT pg_sleep(255);

这个函数会睡眠多久？不要拿出计算器，该函数会睡眠 4分15秒。

为了方便长时间的睡眠，PostgreSQL 提供了另一个函数：

db=# SELECT pg_sleep_for('4 minutes 15 seconds');

不同于它的兄弟函数 pg_sleep，pg_sleep_for 函数接受 interval，比起秒数来说，阅读和理解更加自然。

不使用子查询，获取分组中的第一行或最后一行

当我最初编辑这个列表时，我没有想到这个功能是一个鲜为人知的功能，主要是因为我一直在使用它。但令我惊讶的是，我不断遇到这个问题的奇怪的解决方案，而这个问题可以很容易地用我将要展示给你们的东西来解决，所以我认为它应该在清单上占有一席之地！

假设你有一张学生表：

db=# SELECT * FROM students;

  name  │ class │ height
────────┼───────┼────────
 Haki   │ A     │    186
 Dan    │ A     │    175
 Jax    │ A     │    182
 George │ B     │    178
 Bill   │ B     │    167
 David  │ B     │    178

可以使用以下 CTE 在本节中重新生成查询

WITH students AS (
    SELECT * FROM (VALUES
        ('Haki',    'A',    186),
        ('Dan',     'A',    175),
        ('Jax',     'A',    182),
        ('George',  'B',    178),
        ('Bill',    'B',    167),
        ('David',   'B',    178)
    ) AS t(
        name,       class,  height
    )
)
SELECT * FROM students;

你怎样才能得到每个班级里最高的学生的全部资料？

第一个想法是你可以尝试这样做：

SELECT class, max(height) as tallest
FROM students
GROUP BY class;

 class │ tallest
───────┼─────────
 A     │     186
 B     │     178

这可以得到身高，但是不能得到学生的名字。作为第二次尝试，你可以尝试通过一个子查询来根据身高找到最高的学生：

SELECT *
FROM students
WHERE (class, height) IN (
    SELECT class, max(height) as tallest
    FROM students
    GROUP BY class
);

  name  │ class │ height
────────┼───────┼────────
 Haki   │ A     │    186
 George │ B     │    178
 David  │ B     │    178

现在你获得了每个班级最高学生的全部信息，但是有其他问题

SIDE NOTE

The ability to match a set of records like in the previous query ((class, height) IN (...)), is another lesser known, but a very powerful feature of PostgreSQL.

在班级B中，有两名学生身高一样，并且恰巧也是最高的。使用聚合函数 max 你仅仅可以得到身高，所以你可能会遇到这种情况。

使用 max 的挑战是，你选择的高度只基于高度，在这种情况下是可以的，但你还是要选择一个学生。另一种不同的方法是使用窗口函数，它可以让你根据多个列对行进行"排序"：

SELECT
    students.*,
    ROW_NUMBER() OVER (
        PARTITION BY class
        ORDER BY height DESC, name
    ) AS rn
FROM
    students;

  name  │ class │ height │ rn
────────┼───────┼────────┼────
 Haki   │ A     │    186 │  1
 Jax    │ A     │    182 │  2
 Dan    │ A     │    175 │  3
 David  │ B     │    178 │  1
 George │ B     │    178 │  2
 Bill   │ B     │    167 │  3

要根据学生的身高对他们进行"排名"，你可以为每一行附加一个行号。每个班级 (PARTITION BY class) 确定行号，并按身高降序排列，然后按学生名称(ORDER BY height DESC，name) 排列。除了身高之外，添加学生姓名会使结果更加具有决定性 (假设姓名是唯一的) 。

要得到每个班级中最高的学生的行，你可以使用一个子查询：

SELECT
    name, class, height
FROM (
    SELECT
        students.*,
        ROW_NUMBER() OVER (
            PARTITION BY class
            ORDER BY height DESC, name
        ) AS rn
    FROM
        students
) as inner
WHERE
    rn = 1;

 name  │ class │ height
───────┼───────┼────────
 Haki  │ A     │    186
 David │ B     │    178

你做到了！这就是每个班级最高学生的完整信息。

使用distinct on

现在你已经解决了所有的问题，让我给你展示一个更简单的方式

SELECT DISTINCT ON (class)
    *
FROM
    students
ORDER BY
    class, height DESC, name;

 name  │ class │ height
───────┼───────┼────────
 Haki  │ A     │    186
 David │ B     │    178

很不错，对吧？当我第一次发现 DISTINCT ON 时，我惊呆了。在 Oracle 中，完全没有，据我所知，除了 PostgreSQL 之外，没有其他数据库有这个功能。

直观地理解distinct on

为了理解 DISTINCT ON 是如何工作的，让我们一步一步地了解它是如何工作的。下面是表格中的原始数据：

SELECT *
FROM students;

  name  │ class │ height
────────┼───────┼────────
 Haki   │ A     │    186
 Dan    │ A     │    175
 Jax    │ A     │    182
 George │ B     │    178
 Bill   │ B     │    167
 David  │ B     │    178

下一步，排序数据

SELECT *
FROM students
ORDER BY class, height DESC, name;

  name  │ class │ height
────────┼───────┼────────
 Haki   │ A     │    186
 Jax    │ A     │    182
 Dan    │ A     │    175
 David  │ B     │    178
 George │ B     │    178
 Bill   │ B     │    167

然后，添加 DISTINCT ON 子句

SELECT DISTINCT ON (class) *
FROM students
ORDER BY class, height DESC, name;

为了理解 DISTINCT ON 在这一点上做了什么，我们需要采取两个步骤。

首先，根据 DISTINCT ON 子句中的列将数据分组，在本例中是按班级拆分：

  name  │ class │ height
─────────────────────────
 Haki   │ A     │    186  ┓
 Jax    │ A     │    182  ┣━━ class=A
 Dan    │ A     │    175  ┛

 David  │ B     │    178  ┓
 George │ B     │    178  ┣━━ class=B
 Bill   │ B     │    167  ┛

然后，每一个分组仅保留第一行

  name  │ class │ height
─────────────────────────
 Haki   │ A     │    186  ┣━━ class=A
 David  │ B     │    178  ┣━━ class=B

这就是了! 每个班最高的学生。

DISTINCT ON 唯一的要求是 ORDER BY 子句中的前导列与 DISTINCT ON 子句中的列匹配。ORDER BY 子句中的其余列用于确定为每个分组选择哪一行。

为了说明 ORDER BY 是如何影响结果的，考虑下面这个查询，以找出每个班级中最矮的学生：

SELECT DISTINCT ON (class)
    *
FROM
    students
ORDER BY
    class, height, name;

 name │ class │ height
──────┼───────┼────────
 Dan  │ A     │    175
 Bill │ B     │    167

要获取每个班级中最矮的学生，你只需改变排序顺序，这样每个组的第一行就是最矮的学生。

无需扩展生成UUID

要在 PostgreSQL 中生成 uuid，在版本13之前，你需要使用 uuid-ossp 扩展：

db=# CREATE EXTENSION "uuid-ossp";
CREATE EXTENSION

db=# SELECT uuid_generate_v4() AS uuid;
                 uuid
──────────────────────────────────────
 8e55146d-0ce5-40ab-a346-5dbd466ff5f2

从版本13开始，有一个内置的函数来生成随机 (version 4) uuid：

db=# SELECT gen_random_uuid() AS uuid;
                 uuid
──────────────────────────────────────
 ba1ac0f5-5d4d-4d80-974d-521dbdcca2b2

如果你想生成 version 4 以外的 uuid，则依旧需要 uuid-ossp 扩展。

生成可"复现"的随机数

生成随机数对于演示或测试等许多场景都非常有用。在这两种情况下，能够重现"随机"数据也很有用。

使用 PostgreSQL 随机函数可以产生不同类型的随机数。例如：

db=# SELECT
    random() AS random_float,
    ceil(random() * 10) AS random_int_0_10,
    '2022-01-01'::date + interval '1 days' * ceil(random() * 365) AS random_day_in_2022;

─[ RECORD 1 ]──────┬────────────────────
random_float       │ 0.6031888056092001
random_int_0_10    │ 3
random_day_in_2022 │ 2022-11-10 00:00:00

如果你再次执行这个查询，你会得到不同的结果：

db=# SELECT
    random() AS random_float,
    ceil(random() * 10) AS random_int_0_10,
    '2022-01-01'::date + interval '1 days' * ceil(random() * 365) AS random_day_in_2022;

─[ RECORD 1 ]──────┬────────────────────
random_float       │ 0.7363406030115378
random_int_0_10    │ 2
random_day_in_2022 │ 2022-02-23 00:00:00

为了生成可"复现"的数据，你可以使用setseed：

db=# SELECT setseed(0.4050);
 setseed
─────────

(1 row)

db=# SELECT
    random() AS random_float,
    ceil(random() * 10) AS random_int_0_10,
    '2022-01-01'::date + interval '1 days' * ceil(random() * 365) AS random_day_in_2022
FROM
    generate_series(1, 2);

    random_float    │ random_int_0_10 │ random_day_in_2022
────────────────────┼─────────────────┼─────────────────────
 0.1924247516794324 │               9 │ 2022-12-17 00:00:00
 0.9720620908236377 │               5 │ 2022-06-13 00:00:00

如果你在一个新会话中再次执行相同的块，即使是在不同的数据库中，也会产生完全相同的结果：

otherdb=# SELECT setseed(0.4050);
 setseed
─────────

(1 row)

otherdb=# SELECT
    random() AS random_float,
    ceil(random() * 10) AS random_int_0_10,
    '2022-01-01'::date + interval '1 days' * ceil(random() * 365) AS random_day_in_2022
FROM
    generate_series(1, 2);

    random_float    │ random_int_0_10 │ random_day_in_2022
────────────────────┼─────────────────┼─────────────────────
 0.1924247516794324 │               9 │ 2022-12-17 00:00:00
 0.9720620908236377 │               5 │ 2022-06-13 00:00:00

注意结果是随机的，但仍然是完全一样的。下次你做演示或者共享脚本的时候，一定要包含 setseed，这样你的结果可以很容易的被复现。

添加约束而无需立即校验

约束是任何 RDBMS 不可分割的一部分。约束用于保证数据的一致性和可靠性，并且应该在任何可能的情况下使用。在活生生的系统中，你经常需要添加新的约束，并且添加某些类型的约束可能需要非常激进的锁，这些锁会干扰线上系统的运行。

为了说明，在一个大表上添加一个简单的检查约束：

db=# ALTER TABLE orders ADD CONSTRAINT check_price_gt_zero CHECK (price >= 0);
ALTER TABLE
Time: 10745.662 ms (00:10.746)

此语句对订单的价格添加一个检查约束，以保证价格必须大于等于零。在添加约束的过程中，数据库需要扫描整个表以确保约束对所有已存在的行都有效。整个过程花费了大约10s，并且在期间表被锁住了。

在PostgreSQL中，你可以将添加约束的过程拆解为两个步骤。

首先，添加一个约束并且仅仅校验新的数据，但是不检查现有数据是否有效：

db=# ALTER TABLE orders ADD CONSTRAINT check_price_gt_zero CHECK (price >= 0) NOT VALID;
ALTER TABLE
Time: 13.590 ms

末尾的 NOT VALID 告诉PostgreSQL不要为现有数据去校验是否满足新约束。这意味着数据库不需要扫描整个表。注意，与前一个语句相比，这个语句花费的时间显著减少，几乎是瞬间完成的。

接着，使用一个更加宽松的锁以校验现有数据是否满足约束，该操作允许表上进行其他操作：

db=# ALTER TABLE orders VALIDATE CONSTRAINT check_price_gt_zero;
ALTER TABLE
Time: 11231.189 ms (00:11.231)

请注意，验证约束的时间与添加和验证约束的第一个示例大致相同。这重申了在向现有表添加约束时，大部分时间用于验证现有数据是否满足约束。将整个过程拆分成两个步骤，可以减少表被锁定的时间。

文档还提到了 NOT VALID 的另一个用例——即使存在一些现有的错误值，也只对将来的更新进行约束。也就是说，添加 NOT VALID 并且永远不执行 VALIDATE。

看看这篇来自 Paypal 工程团队的关于在不停机的情况下进行模式更改的文章，以及我自己关于在批量加载期间禁用约束和索引的提示。

PostgreSQL中的同义词

同义词是通过另一个名称引用对象的一种方式，类似于 Linux 中的符号链接。如果你来自 Oracle，你可能对同义词很熟悉，但除此之外，你可能从未听说过它。PostgreSQL 没有名为 "synonyms" 的特性，但这并不意味着这是不可能的。

要让名称引用不同的数据库对象，首先需要了解 PostgreSQL 如何解析绝对名称。比如，如果你使用的 haki 用户连接数据据，并且你引用了一个 foo 表，PostgreSQL会按照这样的顺序搜索下面的对象：

1. 1. haki.foo

2. 2. public.foo

这个顺序由 search_path 参数决定：

db=# SHOW search_path;
   search_path
─────────────────
 "$user", public

第一个值 "$user" 是一个特殊值，它被解析为当前连接的用户名称。第二个值 public 是默认模式的名称。为了演示你可以用search path 做的一些事情，在数据库 db 中创建一个表 foo：

db=# CREATE TABLE foo (value TEXT);
CREATE TABLE

db=# INSERT INTO foo VALUES ('A');
INSERT 0 1

db=# SELECT * FROM foo;
 value
───────
 A
(1 row)

如果出于某些原因你想要 haki 用户引用名称 foo 时查看一个不同的对象，你有两个选项：

在一个名为 haki 的模式中创建一个名为 foo 的对象：

db=# CREATE SCHEMA haki;
CREATE SCHEMA

db=# CREATE TABLE haki.foo (value text);
CREATE TABLE

db=# INSERT INTO haki.foo VALUES ('B');
INSERT 0 1

db=# \conninfo
You are connected to database "db" as user "haki"

db=# SELECT * FROM foo;
value
───────
B

注意，当用户 haki 引用名称 foo 时，PostgreSQL 将名称解析为 haki.foo 而不是 public.foo。这是因为模式 haki 在search path中位于 public 之前。

修改search path：

db=# CREATE SCHEMA synonyms;
CREATE SCHEMA

db=# CREATE TABLE synonyms.foo (value text);
CREATE TABLE

db=# INSERT INTO synonyms.foo VALUES ('C');
INSERT 0 1

db=# SHOW search_path;
   search_path
─────────────────
 "$user", public

db=# SELECT * FROM foo;
 value
───────
 A

db=# SET search_path TO synonyms, "$user", public;
SET

db=# SELECT * FROM foo;
 value
───────
 C

注意，在修改了 search path 以包含模式 synonyms 之后，PostgreSQL 将名称 foo 解析为 synonyms.foo。

为什么同义词有用？

我曾经认为同义词是一种应该避免的代码味道（code smell），但是随着时间的推移，我发现了一些有用的使用案例。其中一个用例便是零停机时间迁移。

当你对线上系统的某个表做更改的时候，你通常需要在同一时刻同时支持新老版本的应用。这提出了一个挑战，因为应用程序的每个版本都希望表具有不同的结构。

以从表中删除列的迁移为例。在迁移运行期间，应用程序的旧版本处于活动状态，并且它期望该列依旧存在于表中，因此你不能简单地移除它。解决这个问题的一种方法是分两个阶段发布新版本——第一个阶段忽略字段，第二个阶段删除字段。

但是，如果需要在单个版本中进行更改，则可以为旧版本提供包含该列的表视图，然后再删除它。为此，你可以使用"同义词"：

db=# \conninfo
You are now connected to database "db" as user "app".

db=# SELECT * FROM users;
 username │ active
──────────┼────────
 haki     │ t

应用以 app 用户连到 db 数据库。你想要移除 active 列，但是应用正在使用该列。为了安全地应用迁移，你需要 "欺骗" app用户，让它以为列仍然在那里，而旧版本是活跃的：

db=# \conninfo
You are now connected to database "db" as user "admin".

db=# CREATE SCHEMA app;
CREATE SCHEMA

db=# GRANT USAGE ON SCHEMA app TO app;
GRANT

db=# CREATE VIEW app.users AS SELECT username, true AS active FROM public.users;
CREATE VIEW

db=# GRANT SELECT ON app.users TO app;
GRANT

为了"欺骗"app用户，你通过该用户名创建了一个模式，并创建了一个包含计算字段 active 的视图。现在，当应用以 app 用户连接时，它看到的是视图而不是表，因此可以安全地删除列：

db=# \conninfo
You are now connected to database "db" as user "admin".

db=# ALTER TABLE users DROP COLUMN active;
ALTER TABLE

db=# \connect db app
You are now connected to database "db" as user "app".

db=# SELECT * FROM users;
 username │ active
──────────┼────────
 haki     │ t

你删除了该列，而应用程序看到的是计算字段！剩下的就是一些清理工作，你就完成了。

找到重叠的范围

假设你有一个会议表：

db=# SELECT * FROM meetings;
       starts_at     │        ends_at
─────────────────────┼─────────────────────
 2021-10-01 10:00:00 │ 2021-10-01 10:30:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00
 2021-10-01 12:30:00 │ 2021-10-01 12:45:00

可以使用以下 CTE 在本节中重新生成查询

WITH meetings AS (
    SELECT
        starts_at::timestamptz AS starts_at,
        ends_at::timestamptz AS ends_at
    FROM (VALUES
        ('2021-10-01 10:00 UTC', '2021-10-01 10:30 UTC'),
        ('2021-10-01 11:15 UTC', '2021-10-01 12:00 UTC'),
        ('2021-10-01 12:30 UTC', '2021-10-01 12:45 UTC')
    ) AS t(
        starts_at,               ends_at)
)
SELECT * FROM meetings;

你想安排一个新的会议，但是在这样做之前，你要确保它不会和另一个会议重叠。你需要考虑以下几种情况：

[A] 新会议在现有会议开始后结束

|-------NEW MEETING--------|
    |*******EXISTING MEETING*******|

[B] 新会议在现有会议结束前开始

        |-------NEW MEETING--------|
|*******EXISTING MEETING*******|

[C] 新会议在现有会议期间举行

    |----NEW MEETING----|
|*******EXISTING MEETING*******|

[D] 现有会议在新会议期间举行

|--------NEW MEETING--------|
    |**EXISTING MEETING**|

[E] 新会议与现有会议的时间完全相同

|--------NEW MEETING--------|
|*****EXISTING MEETING******|

为了测试检查重叠的查询，你可以准备一个包含上面所有场景的表，并尝试一个简单的条件：

WITH new_meetings AS (
    SELECT
        id,
        starts_at::timestamptz as starts_at,
        ends_at::timestamptz as ends_at
    FROM (VALUES
        ('A', '2021-10-01 11:10 UTC', '2021-10-01 11:55 UTC'),
        ('B', '2021-10-01 11:20 UTC', '2021-10-01 12:05 UTC'),
        ('C', '2021-10-01 11:20 UTC', '2021-10-01 11:55 UTC'),
        ('D', '2021-10-01 11:10 UTC', '2021-10-01 12:05 UTC'),
        ('E', '2021-10-01 11:15 UTC', '2021-10-01 12:00 UTC')
    ) as t(
        id,   starts_at,               ends_at
    )
)
SELECT
    *
FROM
    meetings, new_meetings
WHERE
    new_meetings.starts_at BETWEEN meetings.starts_at and meetings.ends_at
    OR new_meetings.ends_at BETWEEN meetings.starts_at and meetings.ends_at;

       starts_at     │        ends_at      │ id │       starts_at     │        ends_at
─────────────────────┼─────────────────────┼────┼─────────────────────┼────────────────────
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ A  │ 2021-10-01 11:10:00 │ 2021-10-01 11:55:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ B  │ 2021-10-01 11:20:00 │ 2021-10-01 12:05:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ C  │ 2021-10-01 11:20:00 │ 2021-10-01 11:55:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ E  │ 2021-10-01 11:15:00 │ 2021-10-01 12:00:00

第一次尝试发现5个场景中有4个场景重叠。它没有检测到方案 D 的重叠，其中新会议在现有会议之前开始，在现有会议之后结束。为了处理这种情况，你需要让这个条件变得更长一些：

WITH new_meetings AS (/* ... */)
SELECT
    *
FROM
    meetings, new_meetings
WHERE
    new_meetings.starts_at BETWEEN meetings.starts_at and meetings.ends_at
    OR new_meetings.ends_at BETWEEN meetings.starts_at and meetings.ends_at
    OR meetings.starts_at BETWEEN new_meetings.starts_at and new_meetings.ends_at
    OR meetings.ends_at BETWEEN new_meetings.starts_at and new_meetings.ends_at;


       starts_at     │        ends_at      │ id │       starts_at     │        ends_at
─────────────────────┼─────────────────────┼────┼─────────────────────┼────────────────────
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ A  │ 2021-10-01 11:10:00 │ 2021-10-01 11:55:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ B  │ 2021-10-01 11:20:00 │ 2021-10-01 12:05:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ C  │ 2021-10-01 11:20:00 │ 2021-10-01 11:55:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ D  │ 2021-10-01 11:10:00 │ 2021-10-01 12:05:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ E  │ 2021-10-01 11:15:00 │ 2021-10-01 12:00:00

这个查询现在检测到5个场景中所有的重叠，但是，考虑一下这些额外的场景：

[E] 新会议在现有会议结束之后立即举行

                            |--------NEW MEETING--------|
|*****EXISTING MEETING******|

[F] 新会议在现有会议开始时立即结束

|--------NEW MEETING--------|
                            |*****EXISTING MEETING******|

紧密相接的会议是非常常见的，不应该发现它们是重叠的。将两个场景添加到测试中，并尝试查询：

WITH new_meetings AS (
    SELECT
        id,
        starts_at::timestamptz as starts_at,
        ends_at::timestamptz as ends_at
    FROM (VALUES
        ('A', '2021-10-01 11:10 UTC', '2021-10-01 11:55 UTC'),
        ('B', '2021-10-01 11:20 UTC', '2021-10-01 12:05 UTC'),
        ('C', '2021-10-01 11:20 UTC', '2021-10-01 11:55 UTC'),
        ('D', '2021-10-01 11:10 UTC', '2021-10-01 12:05 UTC'),
        ('E', '2021-10-01 11:15 UTC', '2021-10-01 12:00 UTC'),
        ('F', '2021-10-01 12:00 UTC', '2021-10-01 12:10 UTC'),
        ('G', '2021-10-01 11:00 UTC', '2021-10-01 11:15 UTC')
    ) as t(
        id,   starts_at,               ends_at
    )
)
SELECT
    *
FROM
    meetings, new_meetings
WHERE
    new_meetings.starts_at BETWEEN meetings.starts_at and meetings.ends_at
    OR new_meetings.ends_at BETWEEN meetings.starts_at and meetings.ends_at
    OR meetings.starts_at BETWEEN new_meetings.starts_at and new_meetings.ends_at
    OR meetings.ends_at BETWEEN new_meetings.starts_at and new_meetings.ends_at;

       starts_at     │        ends_at      │ id │       starts_at     │        ends_at
─────────────────────┼─────────────────────┼────┼─────────────────────┼────────────────────
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ A  │ 2021-10-01 11:10:00 │ 2021-10-01 11:55:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ B  │ 2021-10-01 11:20:00 │ 2021-10-01 12:05:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ C  │ 2021-10-01 11:20:00 │ 2021-10-01 11:55:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ D  │ 2021-10-01 11:10:00 │ 2021-10-01 12:05:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ E  │ 2021-10-01 11:15:00 │ 2021-10-01 12:00:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ F  │ 2021-10-01 12:00:00 │ 2021-10-01 12:10:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ G  │ 2021-10-01 11:00:00 │ 2021-10-01 11:15:00

设想方案 F 和 G 这两个背靠背的会议被错误地归类为重叠。这是因为操作符 BETWEEN操作符是in clusive的（译者著：between是区间，包含两个边界值，类似于[10,20]） 。要在不使用 BETWEEN 的情况下实现这个条件，你必须这样做：

WITH new_meetings AS (/* ... */)
SELECT
    *
FROM
    meetings, new_meetings
WHERE
    (new_meetings.starts_at > meetings.starts_at AND new_meetings.starts_at < meetings.ends_at)
    OR
    (new_meetings.ends_at > meetings.starts_at AND new_meetings.ends_at < meetings.ends_at)
    OR
    (meetings.starts_at > new_meetings.starts_at AND meetings.starts_at < new_meetings.ends_at)
    OR
    (meetings.ends_at > new_meetings.starts_at AND meetings.ends_at < new_meetings.ends_at)
    OR
    (meetings.starts_at = new_meetings.starts_at AND meetings.ends_at = new_meetings.ends_at);

       starts_at     │        ends_at      │ id │       starts_at     │        ends_at
─────────────────────┼─────────────────────┼────┼─────────────────────┼────────────────────
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ A  │ 2021-10-01 11:10:00 │ 2021-10-01 11:55:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ B  │ 2021-10-01 11:20:00 │ 2021-10-01 12:05:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ C  │ 2021-10-01 11:20:00 │ 2021-10-01 11:55:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ D  │ 2021-10-01 11:10:00 │ 2021-10-01 12:05:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ E  │ 2021-10-01 11:15:00 │ 2021-10-01 12:00:00

查询正确地将场景 A-E 识别为重叠，并且不将紧密相接的场景 F 和 G 识别为重叠。这就是你想要的。然而，这种情况是相当疯狂的！它很容易失去控制。

这就是 PostgreSQL 中下面的操作符证明自己非常有价值的地方：

WITH new_meetings AS (
    SELECT
        id,
        starts_at::timestamptz as starts_at,
        ends_at::timestamptz as ends_at
    FROM (VALUES
        ('A', '2021-10-01 11:10 UTC', '2021-10-01 11:55 UTC'),
        ('B', '2021-10-01 11:20 UTC', '2021-10-01 12:05 UTC'),
        ('C', '2021-10-01 11:20 UTC', '2021-10-01 11:55 UTC'),
        ('D', '2021-10-01 11:10 UTC', '2021-10-01 12:05 UTC'),
        ('E', '2021-10-01 11:15 UTC', '2021-10-01 12:00 UTC'),
        ('F', '2021-10-01 12:00 UTC', '2021-10-01 12:10 UTC'),
        ('G', '2021-10-01 11:00 UTC', '2021-10-01 11:15 UTC')
    ) as t(
        id,   starts_at,               ends_at
    )
)
SELECT
    *
FROM
    meetings, new_meetings
WHERE
    (new_meetings.starts_at, new_meetings.ends_at)
        OVERLAPS (meetings.starts_at, meetings.ends_at);

       starts_at     │        ends_at      │ id │       starts_at     │        ends_at
─────────────────────┼─────────────────────┼────┼─────────────────────┼────────────────────
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ A  │ 2021-10-01 11:10:00 │ 2021-10-01 11:55:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ B  │ 2021-10-01 11:20:00 │ 2021-10-01 12:05:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ C  │ 2021-10-01 11:20:00 │ 2021-10-01 11:55:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ D  │ 2021-10-01 11:10:00 │ 2021-10-01 12:05:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ E  │ 2021-10-01 11:15:00 │ 2021-10-01 12:00:00

就是这样！使用 OVERLAPS 操作符，您可以替换这5个复杂的条件，并保持查询简短，易于阅读和理解。

原文译自：https://hakibenita.com/postgresql-unknown-features