翻译：PostgreSQL 鲜为人知的特性-阿里云开发者社区

原文链接 https://hakibenita.com/postgresql-unknown-features
这些特性你早已拥有，只是未曾发现！

2006年，微软曾开展一项客户调研，旨在了解用户希望微软Office新版本新增哪些功能。令他们意外的是，用户提出的需求中超过90%其实早已存在，只是用户自己不知道而已。为了解决这种“功能可发现性”问题，微软推出了如今我们在Office产品中看到的“功能区界面（Ribbon UI）”。

不止Office如此，我们大多数人都无法知晓日常使用工具的全部功能——尤其是像PostgreSQL这样庞大且功能丰富的工具。现在带大家了解PostgreSQL中那些早已存在、但你可能从未留意的小众特性。

本文将为你介绍这些PostgreSQL鲜为人知的特性。

1. 获取Upsert操作中更新和插入的行数

INSERT ON CONFLICT（在Oracle中也称为“merge”，或“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

该查询会向表中插入新的员工数据；如果尝试插入的员工姓名已存在，则会更新该行数据。

`RETURNING *`

你可以学习如何结合WITH和RETURNING实现完整的业务流程。

从上述命令的输出INSERT 0 2中，你能看到有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

注意RETURNING子句的变化：它新增了计算字段inserted，该字段通过特殊列xmax判断行是插入还是更新。从返回结果可以看出，“Jane”是新插入的行（inserted = t），而“George”已存在，因此是更新操作（inserted = f）。

xmax是PostgreSQL的特殊系统列，其含义为：

删除该行版本的事务ID；若该行未被删除，则值为0。

在PostgreSQL中，更新一行时会先删除旧版本，xmax会记录执行删除操作的事务ID；而插入行时没有旧版本需要删除，因此xmax = 0。这个“小技巧”正是利用这一特性区分插入和更新的行。

2. 为特定列授予权限

假设你有一个users表，包含凭证、密码、个人身份信息（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用户可以访问整个users表：

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

如前所述，分析师仅需访问非敏感数据生成报表，因此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

撤销全表的SELECT权限后，仅授予analyst对id和username列的查询权限。此时分析师无法访问受限列：

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尝试显式/隐式（通过*）访问受限列时，会触发“权限拒绝”错误。

3. 匹配多个模式

SQL中的模式匹配很常见，例如查找所有使用gmail邮箱的用户：

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

该查询通过通配符%匹配以@gmail.com结尾的邮箱。如果需要同时匹配gmail.com和yahoo.com，常规写法是用OR拼接：

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

而PostgreSQL提供了更简洁的方式——使用SIMILAR TO匹配多个模式：

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

SIMILAR TO支持用|分隔多个匹配模式，让查询更简洁。

另一种方式是使用正则表达式：

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

使用正则时需注意：.是通配符，需用\.转义才能匹配邮箱中的点号。

我在推特分享这一技巧时，PostgreSQL的Python驱动psycopg官方账号还提供了另一种写法：

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

该写法通过ANY运算符匹配数组中的任意模式，更适合在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支持将模式列表绑定为变量，灵活性更高。

4. 获取序列当前值而不推进序列

如果你需要获取序列的当前值，第一反应可能是用currval：

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

和大多数人一样，你会发现currval仅在当前会话中使用过该序列后才有效。为了查值而无意义地推进序列显然不可取，因此这不是理想方案。

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

这个视图能满足需求，但它只是信息模式中的普通表，算不上“小众特性”。

另一种方式是使用未文档化的函数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——ID管理应使用nextval。

5. 结合\copy使用多行SQL

频繁使用psql的开发者常会用\COPY导出数据，但\COPY的一大痛点是不支持多行查询：

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

换行时会触发解析错误。

最初我用视图解决这个问题：

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;

这种方法可行，但如果中途出错，视图会残留。为了自动清理，我改用临时视图：

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客户端命令，不支持多行。
输出到标准输出（STDOUT）：COPY可将结果写入服务器目录，或通过TO STDOUT输出到标准输出。
用\g将STDOUT写入本地文件：psql的\g命令可将标准输出内容写入指定文件。

三者结合完美解决了多行查询导出的问题。

💡 进阶技巧：如果需要高频迁移数据，可了解通过Python向PostgreSQL批量加载数据的最快方式。

6. 禁止手动设置自动生成的主键值

在PostgreSQL中使用自动生成主键时，你可能仍在使用SERIAL类型：

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

PostgreSQL会在后台创建序列，用于插入行时生成ID：

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 Column）：

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

标识列的行为与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，序列未被使用，值仍为1；
第二条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，直接报错！

错误提示中，PostgreSQL还贴心给出了“确实需要手动设置”的解决方案：

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

INSERT 0 1

添加OVERRIDING SYSTEM VALUE子句后，可显式允许手动设置标识列的值（仍需自行处理主键冲突）。

7. 生成透视表的另外两种方法

我曾在之前的文章中演示过用条件聚合生成透视表，后来又发现了PostgreSQL中另外两种生成透视表的方法。

假设你需要统计各部门不同岗位的员工数量：

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

将结果转为透视表会更直观。在psql中，可使用\crosstabview命令将上一次查询结果转为透视表：

db=# \crosstabview

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

默认情况下，\crosstabview会用前两列生成透视表，也可通过参数指定列：

db=# \crosstabview department role

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

另一种方法是使用内置的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函数的缺点是需要提前定义输出列，但优点是返回的是表对象，可作为子查询进一步处理。

8. 美元符号引用（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 ;)

字符串首尾的$$`是分隔符，中间的内容无需转义。如果文本本身包含`$$，还可以添加自定义标签：

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`对象： ```sql 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`对象可自由操作。

---

### 9. 为数据库对象添加注释
PostgreSQL支持为几乎所有数据库对象添加注释。例如，为表添加注释：
```sql
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 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$;

此后无需查阅文档，直接通过psql查看函数注释：

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                                                                      
            │

10. 为每个数据库保留独立的历史命令文件

使用CLI工具时，你大概率会频繁搜索历史命令（如bash/psql中按CTRL + R反向搜索）。如果同时操作多个数据库，可为每个数据库配置独立的历史文件：

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

这样，你在不同数据库中搜索历史命令时，能更快找到相关内容。将该配置写入~/.psqlrc可永久生效。

11. 自动补全大写的保留字

关于SQL关键字应大写还是小写，一直存在大量讨论（甚至玩笑）。如果你和我一样偏好大写关键字，psql提供了自动补全大写关键字的配置：

db=# selec <tab>  -- 未配置前，补全为小写
db=# select

db=# \set COMP_KEYWORD_CASE upper
db=# selec <tab>  -- 配置后，补全为大写
db=# SELECT

设置COMP_KEYWORD_CASE = upper后，按TAB自动补全的关键字会变为大写。

12. 按时间间隔休眠

延迟程序执行在测试、限流等场景中很实用。PostgreSQL中常用pg_sleep实现休眠：

db=# \timing
Timing is on.

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

(1 row)

Time: 3014.913 ms (00:03.015)

pg_sleep接收秒数作为参数，但休眠时间较长时，计算秒数会很麻烦（例如pg_sleep(255)对应4分15秒）。

PostgreSQL提供了更友好的pg_sleep_for函数，支持直接传入时间间隔：

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

相比pg_sleep，pg_sleep_for的参数更符合人类阅读习惯。

13. 无需子查询获取分组中的首行/末行

我原本没把这个特性归为“小众”（因为我经常用），但发现很多人仍在用复杂方案解决这个问题，因此值得一提。

假设有学生表：

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

如何获取每个班级中身高最高的学生完整信息？

初步尝试：

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

💡 补充：(class, height) IN (...)这种多列匹配是PostgreSQL的小众但强大的特性。

该查询返回了所有身高最高的学生，但B班有两名学生身高相同，若只想选其中一位，可使用窗口函数：

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

ROW_NUMBER()按班级分区（PARTITION BY class），先按身高降序、再按姓名排序，为每行分配行号（姓名保证结果确定性）。

嵌套子查询筛选行号为1的记录：

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

这已经能满足需求，但PostgreSQL提供了更简洁的方式——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是PostgreSQL特有语法（其他数据库无此特性），其执行逻辑可拆解为：

按ORDER BY排序数据：

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

按DISTINCT ON (class)分组；
保留每个分组的第一行。

DISTINCT ON的唯一要求是：ORDER BY的前导列必须与DISTINCT ON的列一致，后续列用于决定分组内的首行。

例如，获取每个班级最矮的学生：

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

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

只需修改排序规则，即可获取分组内的末行/特定行。

14. 无需扩展生成UUID

PostgreSQL 13之前，生成UUID需依赖uuid-ossp扩展：

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

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

PostgreSQL 13新增了内置函数gen_random_uuid()，可直接生成随机（版本4）UUID：

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

若需生成非版本4的UUID，仍需使用uuid-ossp扩展。

15. 生成可复现的随机数据

生成随机数据在演示、测试中很常用，而“可复现”的随机数据更有价值。

PostgreSQL的random()函数可生成各类随机数据：

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

重复执行会得到不同结果，但通过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可让结果轻松复现。

16. 添加约束但不立即验证

约束是关系型数据库的核心特性，用于保证数据的清洁性和可靠性。在生产系统中添加新约束时，全表验证可能导致长时间锁表，影响业务。

例如，为大表添加检查约束：

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

该操作会扫描全表验证现有数据，耗时约10秒，且期间表被锁定。

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即可。

17. PostgreSQL中的“同义词”实现

同义词（Synonym）允许用别名引用数据库对象（类似Linux的软链接）。如果你熟悉Oracle，对同义词一定不陌生；PostgreSQL虽无“同义词”特性，但可通过其他方式实现类似效果。

要实现别名，需先了解PostgreSQL的非限定名解析规则：例如，用户haki连接数据库后查询foo表，PostgreSQL会按以下顺序查找：

haki.foo（用户同名模式）
public.foo（默认模式）

该顺序由search_path参数决定：

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

$user是特殊值，解析为当前连接用户；public是默认模式。

实现方式1：创建用户同名模式

-- 创建public.foo表
db=# CREATE TABLE foo (value TEXT);
CREATE TABLE
db=# INSERT INTO foo VALUES ('A');
INSERT 0 1
db=# SELECT * FROM foo;
 value
───────
 A

-- 创建haki模式及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

-- 以haki用户连接，查询foo会优先匹配haki.foo
db=# \conninfo
You are connected to database "db" as user "haki"
db=# SELECT * FROM foo;
value
───────
B

实现方式2：修改search_path

-- 创建synonyms模式及synonyms.foo表
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

-- 默认search_path下，foo匹配public.foo
db=# SHOW search_path;
   search_path
─────────────────
 "$user", public
db=# SELECT * FROM foo;
 value
───────
 A

-- 修改search_path，优先匹配synonyms.foo
db=# SET search_path TO synonyms, "$user", public;
SET
db=# SELECT * FROM foo;
 value
───────
 C

同义词的实用场景：零停机迁移

在生产表变更时，常需同时支持新旧版本应用（不同版本期望表结构不同）。例如，需删除users表的active列，但旧版本应用仍依赖该列：

-- 以app用户连接，当前表结构
db=# \conninfo
You are now connected to database "db" as user "app".
db=# SELECT * FROM users;
 username │ active
──────────┼────────
 haki     │ t

-- 以admin用户创建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

-- 删除public.users的active列
db=# ALTER TABLE users DROP COLUMN active;
ALTER TABLE

-- 以app用户查询users，实际访问的是app.users视图
db=# \connect db app
You are now connected to database "db" as user "app".
db=# SELECT * FROM users;
 username │ active
──────────┼────────
 haki     │ t

通过“同义词”（视图+search_path），旧版本应用无需修改即可正常运行，实现零停机迁移。

18. 查找重叠的时间/数值范围

假设有会议表：

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

安排新会议前，需检查是否与现有会议重叠。重叠场景包括：

A：新会议开始于现有会议中，结束于其外；
B：新会议结束于现有会议中，开始于其外；
C：新会议完全包含于现有会议；
D：现有会议完全包含于新会议；
E：新会议与现有会议完全重合；
F/G：新会议与现有会议首尾相接（非重叠，需排除）。

初始尝试：用BETWEEN判断

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;

该查询会错误地将F/G（首尾相接）判定为重叠（因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);

该查询能正确识别重叠/非重叠，但条件过于繁琐。

最优方案：OVERLAPS运算符

PostgreSQL提供的OVERLAPS运算符可简洁解决范围重叠问题：

WITH new_meetings AS (/* 同上 */)
SELECT *
FROM meetings, new_meetings
WHERE
    (new_meetings.starts_at, new_meetings.ends_at)
        OVERLAPS (meetings.starts_at, meetings.ends_at);

OVERLAPS会自动处理所有重叠场景，且排除首尾相接的非重叠场景，代码简洁且易维护。

翻译：PostgreSQL 鲜为人知的特性

1. 获取Upsert操作中更新和插入的行数

`RETURNING *`

2. 为特定列授予权限

3. 匹配多个模式

4. 获取序列当前值而不推进序列

5. 结合\copy使用多行SQL

6. 禁止手动设置自动生成的主键值

7. 生成透视表的另外两种方法

8. 美元符号引用（Dollar Quoting）

10. 为每个数据库保留独立的历史命令文件

11. 自动补全大写的保留字

12. 按时间间隔休眠

13. 无需子查询获取分组中的首行/末行

14. 无需扩展生成UUID

15. 生成可复现的随机数据

16. 添加约束但不立即验证

17. PostgreSQL中的“同义词”实现

实现方式1：创建用户同名模式

实现方式2：修改search_path

同义词的实用场景：零停机迁移

18. 查找重叠的时间/数值范围

初始尝试：用BETWEEN判断

改进尝试：用开区间判断

最优方案：OVERLAPS运算符

热门文章

最新文章

相关电子书

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

翻译：PostgreSQL 鲜为人知的特性

1. 获取Upsert操作中更新和插入的行数

RETURNING *

2. 为特定列授予权限

3. 匹配多个模式

4. 获取序列当前值而不推进序列

5. 结合\copy使用多行SQL

6. 禁止手动设置自动生成的主键值

7. 生成透视表的另外两种方法

8. 美元符号引用（Dollar Quoting）

10. 为每个数据库保留独立的历史命令文件

11. 自动补全大写的保留字

12. 按时间间隔休眠

13. 无需子查询获取分组中的首行/末行

14. 无需扩展生成UUID

15. 生成可复现的随机数据

16. 添加约束但不立即验证

17. PostgreSQL中的“同义词”实现

实现方式1：创建用户同名模式

实现方式2：修改search_path

同义词的实用场景：零停机迁移

18. 查找重叠的时间/数值范围

初始尝试：用BETWEEN判断

改进尝试：用开区间判断

最优方案：OVERLAPS运算符

热门文章

最新文章

相关电子书

`RETURNING *`