PostgreSQL 实例教程

Dariush Abbasi
简体中文版 · 面向 PostgreSQL 17

本书用 57 节短课，从第一次查询讲起，依次介绍数据建模、查询、索引、事务、存储逻辑、检索与日常运维。每章都附有 SQL 文件；备份一章介绍 Shell 命令，psql 参考一章则提供查询系统目录的替代示例。

开始阅读 · 整书打印 · 源码仓库

准备工作

本书面向 PostgreSQL 17，使用 psql 命令行客户端。更高版本才有的功能会明确标注。阅读时可以配合查阅官方文档。

下载完整 SQL 示例包，解压到一个新目录，然后在包含 source/ 的目录中运行命令。请使用可以随时丢弃的练习数据库：本书会创建、修改和删除示例表。示例中的数据库名为 postgres，运行时请替换成自己的练习数据库，例如 pbe：

createdb pbe
psql -X -v ON_ERROR_STOP=1 -f source/first-query.sql pbe

请按章序阅读。在学习第 6 章之前，先用第 16 章和第 24 章介绍的文件创建示例表：

psql -X -v ON_ERROR_STOP=1 -f source/create-table.sql -f source/insert.sql pbe

第 16 章会重新创建表，第 24 章会再次填入数据。后面的连接章节依赖第 29 章的示例，递归示例还会用到第 31 章。跳读时，请先完成该章要求的准备步骤。选做练习可能改变后续示例的结果。日期、UUID、系统目录列表、查询计划和执行耗时可能不同；终端记录中的省略号表示省去的输出。

目录

示例

1. 示例 1.1 — 第一次查询
2. 示例 2.1 — psql 基础
3. 示例 3.1 — SELECT 基础
4. 示例 4.1 — WHERE
5. 示例 5.1 — ORDER BY
6. 示例 6.1 — 从表中查询数据
7. 示例 7.1 — LIMIT 与 OFFSET
8. 示例 8.1 — DISTINCT
9. 示例 9.1 — 空值（NULL）
10. 示例 10.1 — 表达式
11. 示例 11.1 — 数值类型
12. 示例 12.1 — 文本类型
13. 示例 13.1 — 布尔值与日期
14. 示例 14.1 — UUID 与 JSONB
15. 示例 15.1 — 数组与 ENUM
16. 示例 16.1 — CREATE TABLE
17. 示例 17.1 — 列类型与约束
18. 示例 18.1 — ALTER TABLE 与 DROP
19. 示例 19.1 — 主键与唯一约束
20. 示例 20.1 — 外键
21. 示例 21.1 — NOT NULL 与 DEFAULT
22. 示例 22.1 — 标识列与序列
23. 示例 23.1 — 模式与 search_path
24. 示例 24.1 — INSERT
25. 示例 25.1 — UPSERT（ON CONFLICT）
26. 示例 26.1 — UPDATE
27. 示例 27.1 — DELETE
28. 示例 28.1 — RETURNING
29. 示例 29.1 — 内连接与左连接
30. 示例 30.1 — 右连接与全连接
31. 示例 31.1 — 自连接
32. 示例 32.1 — UNION、INTERSECT 与 EXCEPT
33. 示例 33.1 — COUNT、SUM 与 AVG
34. 示例 34.1 — GROUP BY
35. 示例 35.1 — HAVING
36. 示例 36.1 — 标量子查询与 IN 子查询
37. 示例 37.1 — EXISTS 与派生表
38. 示例 38.1 — 公用表表达式（WITH）
39. 示例 39.1 — 窗口函数
40. 示例 40.1 — 字符串与数值函数
41. 示例 41.1 — 日期函数与 COALESCE
42. 示例 42.1 — CASE
43. 示例 43.1 — 创建索引
44. 示例 44.1 — 何时创建索引
45. 示例 45.1 — EXPLAIN 与查询计划
46. 示例 46.1 — BEGIN、COMMIT 与 ROLLBACK
47. 示例 47.1 — 保存点
48. 示例 48.1 — 创建视图
49. 示例 49.1 — 物化视图
50. 示例 50.1 — 函数与 PL/pgSQL
51. 示例 51.1 — 触发器
52. 示例 52.1 — 全文检索
53. 示例 53.1 — VACUUM 与 ANALYZE
54. 示例 54.1 — pg_dump 与 pg_restore
55. 示例 55.1 — 角色与 GRANT
56. 示例 56.1 — psql 元命令
57. 示例 57.1 — COPY

关于本版

本中文版译自英文网页修订版。该英文版以 boringcollege/postgres-by-example 仓库的提交 ffde0d358722498e37982b57bdbd711871a61c43 为基础，将讲解与 SQL 示例放在一起，已核查的技术修订记录在编校说明中。原仓库采用 Creative Commons 署名 4.0 国际许可协议。

中文版由 Codex 翻译并分轮自检，未经独立人工审定。

出版与许可 · 技术勘误

1 第一次查询

要确认 PostgreSQL 环境是否正常，最快的办法就是运行一个不需要表的查询。PostgreSQL 可以单独计算表达式，并不是每条 SELECT 都需要 FROM。我们将利用这一点，一次确认三件事：服务器正在运行，客户端能够连接到它，而且版本足够新，可以跟着本书练习。

本章要点

• 如何用 psql -f 运行 SQL 文件
• 不需要表的最简 SELECT
• 如何读懂 PostgreSQL 版本字符串
• 客户端版本与服务器版本的区别

示例

-- Simplest query: no tables, just a literal
SELECT 1;

-- Arithmetic and string expressions work without a FROM
SELECT 2 + 3 AS sum, 'hello, postgres' AS greeting;

-- Check your PostgreSQL server version
SELECT version();

-- A more readable version, when you only need the number
SHOW server_version;

SELECT 1 返回一行一列，值为整数 1。表达式形式（SELECT 2 + 3, 'hello, postgres'）表明，SELECT 中可以直接写任意表达式，由 PostgreSQL 求值并返回结果。version() 是一个内置函数，会返回完整的版本字符串，其中包括操作系统、编译器等信息；SHOW server_version 则只返回版本号。两者报告的都是当前所连接服务器的版本。psql --version 报告的是客户端版本，两者可能不同。

运行示例

在仓库根目录运行这个文件：

$ psql -f source/first-query.sql postgres
 ?column?
----------
        1
(1 row)

 sum | greeting
-----+-----------------
   5 | hello, postgres
(1 row)

                                                    version
------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 17.x on x86_64-pc-linux-gnu, compiled by gcc ...
(1 row)

 server_version
----------------
 17.x
(1 row)

你看到的版本字符串会有所不同。如果输出与上面类似，就说明连接已经打通了。

常见陷阱

• “could not connect to server” —— 守护进程没有运行。可用 brew services start postgresql（macOS）、sudo systemctl start postgresql（Linux），或安装方式所对应的命令启动它。
• “role ‘you’ does not exist” —— PostgreSQL 中没有与你的操作系统用户对应的数据库用户。请在能够访问该集群的 Shell 中运行 createuser --superuser $(whoami) 创建用户。
• “database ‘postgres’ does not exist” —— 某些安装方式，尤其是 macOS 上的 homebrew，不会创建默认的 postgres 数据库。运行一次 createdb postgres，或用 createdb pbe 创建练习库，并在每条命令中将数据库名指定为 pbe。

下载

SQL 文件：first-query.sql · 全部示例文件 · 准备工作

2 psql 基础

psql 是 PostgreSQL 官方的命令行客户端，非常好用。大多数专业用户都会优先使用它，而不是图形界面工具：它速度快，支持脚本，而且每次安装 PostgreSQL 都会附带。它有两种用法：以批处理模式（psql -f file.sql）运行脚本，或者进入交互模式（psql），逐条输入 SQL 和元命令。本章介绍基础用法；后面的章节会介绍更多元命令。

本章要点

• psql 的两种模式：文件模式与交互模式
• 如何确认当前会话的身份与环境（当前用户、数据库、模式）
• SQL 语句与 psql 元命令的区别
• 如何获取帮助：用 \? 查看元命令帮助，用 \h 查看 SQL 帮助

示例

-- List user databases. Equivalent to \l in interactive psql.
SELECT datname
FROM pg_database
WHERE datistemplate = false
ORDER BY datname
LIMIT 5;

-- Identify the session: who am I, where am I, what's on my search_path?
SELECT current_user, current_database(), current_schema();

-- A few server settings worth knowing about
SHOW timezone;
SHOW server_encoding;

pg_database 是众多系统目录之一，也就是 PostgreSQL 提供给用户查看的内部表。datistemplate = false 这个过滤条件会隐藏 template0 和 template1，新数据库都是从这两个模板克隆而来的。current_user、current_database() 和 current_schema() 是会话函数；排查连接或权限问题时，你经常会需要它们提供的信息。SHOW 显示运行时参数，这些参数可以在服务器、数据库、角色或会话级别修改。

交互式使用 psql 时，还可以输入由客户端在本地解释的元命令（也称反斜线命令）：

\l         list databases
\c db      connect to database `db`
\dt        list visible tables in the current search_path
\d table   describe a table (columns, types, indexes)
\dn        list schemas
\df        list functions
\du        list roles (users/groups)
\timing    toggle showing query timing
\x         toggle expanded display (great for wide rows)
\?         help for meta-commands
\h SELECT  help for the SELECT command (or any SQL keyword)
\q         quit

无论是在交互模式中，还是在通过 psql -f 读取的文件中，元命令都由 psql 客户端解释。\dt 等命令会向服务器发送系统目录查询；反斜线语法本身并不是服务器端的 SQL。

运行示例

运行这个文件：

$ psql -f source/psql-basics.sql postgres
       datname
--------------------
 postgres
(1 row)

 current_user | current_database | current_schema
--------------+------------------+----------------
 your_user    | postgres         | public
(1 row)

 TimeZone
----------
 UTC
(1 row)

 server_encoding
-----------------
 UTF8
(1 row)

常见陷阱

• 元命令需要 psql 客户端。psql -f 可以执行文件中的 \dt；如果驱动或图形界面工具直接把文件作为 SQL 发送，就不会解释这个命令。
• 混淆 current_user 与 session_user：当 SET ROLE 生效时，current_user 反映当前生效的角色，而 session_user 仍是最初登录的用户。
• 编码不匹配：如果终端不是 UTF-8 编码，而数据库是，就可能出现乱码。将 client_encoding 设为与终端一致的编码。

下载

SQL 文件： psql-basics.sql · 全部示例文件 · 准备工作

3 SELECT 基础

SELECT 是 SQL 的核心，用来读取数据。最简单的形式是没有 FROM 的 SELECT expression，它对表达式求值并返回一行。更常见的形式 SELECT columns FROM table 则对表中每个匹配的行返回一行结果。无论哪种形式，结果都是一个关系，由行和列组成，列名可以用 AS 修改。

本章要点

• 在 SELECT 中使用字面量与表达式（无需表）
• 用 AS 为结果列命名
• 在一次查询中选择多列
• 读取系统目录表
• 列别名与表别名的区别

示例

-- Literals and expressions: no FROM required
SELECT 1 AS one;
SELECT 2 + 3 AS sum, 10 / 4 AS quotient, 10 / 4.0 AS real_quotient;
SELECT 'hello' AS greeting, length('hello') AS chars;

-- Multiple columns in one row
SELECT 1 AS a, 2 AS b, 'x' AS c;

-- Reading from a table (here, a system catalog)
SELECT relname, relkind
FROM pg_class
WHERE relname = 'pg_database'
LIMIT 1;

每条 SELECT 都会产生一个结果集。AS column_name 为输出列重新命名；如果省略它，PostgreSQL 会为字面量生成 ?column? 这样的名称，或沿用源列名。算术运算那一行值得再看一遍：10 / 4 返回整数 2，因为两个操作数都是整数；而 10 / 4.0 返回 2.5，因为其中一个操作数是 numeric 类型，不再使用整数除法。计算平均值和比率时，经常会遇到这个陷阱。

pg_class 是列出关系的系统目录，其中包括表、索引、视图、序列、物化视图等。relkind 列用于区分它们（r = 普通表，i = 索引，v = 视图，m = 物化视图，S = 序列）。查看系统目录是个值得养成的习惯，关于你所用模式的全部信息，都能在这里找到。

运行示例

运行方式：

$ psql -f source/select-basics.sql postgres
 one
-----
   1
(1 row)

 sum | quotient | real_quotient
-----+----------+---------------
   5 |        2 |            2.5
(1 row)

 greeting | chars
----------+-------
 hello    |     5
(1 row)

 a | b | c
---+---+---
 1 | 2 | x
(1 row)

   relname   | relkind
-------------+---------
 pg_database | r
(1 row)

常见陷阱

• 忘记给表达式加 AS：SELECT count(*) 可以正常运行，但 SELECT count(*) FROM ... WHERE total > 0 GROUP BY id 如果没有 AS order_count 就会很难读懂。一定要为派生列设置别名。
• 整数除法：1 / 2 返回 0。对其中一侧做类型转换：1::numeric / 2，或写成 1.0 / 2。
• 双引号与单引号：'hello' 是字符串字面量，"hello" 是标识符（列名或表名）。混淆两者是 SQL 初学者最常犯的错误之一。

下载

SQL 文件： select-basics.sql · 全部示例文件 · 准备工作

4 WHERE

WHERE 用于过滤行。这个子句位于 FROM 与 ORDER BY 之间，PostgreSQL 只保留条件求值为真的行。结果为假或 NULL 的行都会被排除。这种三值逻辑是 SQL 的一个特别之处，也是许多初学者出错的根源，最好尽早熟悉它。

本章要点

• 用比较操作符（=、<>、<、>、<=、>=）过滤
• 用 LIKE 做模式匹配（不区分大小写时用 ILIKE）
• 用 AND、OR、NOT 组合条件
• 成员判断与范围：IN、BETWEEN
• 为什么 = NULL 不起作用，以及该用什么替代

示例

-- Equality and ordering
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname = 'pg_catalog'
ORDER BY tablename
LIMIT 3;

-- Pattern matching: % matches any sequence, _ matches one character
SELECT tablename
FROM pg_tables
WHERE tablename LIKE 'pg_a%'
ORDER BY tablename
LIMIT 5;

-- Case-insensitive: ILIKE (PostgreSQL extension)
SELECT tablename FROM pg_tables WHERE tablename ILIKE 'PG_Class' LIMIT 1;

-- Multiple conditions
SELECT tablename
FROM pg_tables
WHERE schemaname = 'pg_catalog'
  AND tablename LIKE 'pg_%'
  AND tablename NOT LIKE 'pg_stat_%'
ORDER BY tablename
LIMIT 5;

-- IN: membership test
SELECT tablename
FROM pg_tables
WHERE tablename IN ('pg_class', 'pg_database', 'pg_namespace')
ORDER BY tablename;

-- BETWEEN: inclusive range
SELECT n FROM generate_series(1, 10) AS n WHERE n BETWEEN 3 AND 7;

LIKE 是 SQL 标准中的模式匹配操作符：% 匹配任意字符串（包括空字符串），_ 恰好匹配一个字符。ILIKE 是 PostgreSQL 提供的扩展，用于不区分大小写的匹配。能否使用索引取决于匹配模式、排序规则和操作符类。字母前缀通常需要合适的表达式索引或三字符组（trigram）索引；对于以不受大小写影响的字符开头的某些匹配模式，B-树也能提供支持。若需要更强大的匹配能力，PostgreSQL 还支持 ~（正则表达式）、~*（不区分大小写的正则表达式），以及后面会介绍的全文检索。

AND 的优先级高于 OR，所以 A AND B OR C 的含义是 (A AND B) OR C。拿不准时就加括号，日后再读这条查询时，你会感谢现在的自己。IN (...) 是 = ANY (ARRAY[...]) 的简写，等价于一串用 OR 连接的条件。

运行示例

运行方式：

$ psql -f source/where.sql postgres
 schemaname |  tablename
------------+--------------
 pg_catalog | pg_aggregate
 pg_catalog | pg_am
 pg_catalog | pg_amop
(3 rows)
 ...

常见陷阱

• = NULL 和 <> NULL 永远不会为真（结果是 NULL）。请用 IS NULL 和 IS NOT NULL。从其他各种语言转来的工程师都容易在这里栽跟头。
• 如果子查询包含 NULL，NOT IN (subquery) 的行为就会变得难以理解：不匹配的值会得到 NULL，匹配的值会得到假，因此过滤后不会保留任何行。对于可能返回空值的子查询，优先使用 NOT EXISTS。
• LIKE 'abc' 不会匹配 'ABC'。请用 ILIKE，或对两侧都应用 LOWER()；不过，LOWER(col) = 'abc' 无法利用 col 上的普通索引（如果经常需要这样查询，可在 LOWER(col) 上建立表达式索引）。
• 小心使用 IN ('a', 'b', NULL)：列表中的 NULL 没有带来任何好处，却会悄悄让 NOT IN 的行为出乎意料。

下载

SQL 文件： where.sql · 全部示例文件 · 准备工作

5 ORDER BY

ORDER BY 对结果集排序。如果没有它，行的顺序就是未定义的：PostgreSQL 可以按规划器认为方便的任意顺序返回行，而且每次运行的顺序都可能不同。如果应用关心顺序，就要明确指定。

本章要点

• 用 ORDER BY column 排序
• ASC（默认）与 DESC
• 多列排序（打破并列）
• 控制空值的位置（NULLS FIRST / NULLS LAST）
• 按表达式和位置排序

示例

-- Default ascending
SELECT tablename
FROM pg_tables
WHERE schemaname = 'pg_catalog'
ORDER BY tablename
LIMIT 4;

-- Descending
SELECT tablename
FROM pg_tables
WHERE schemaname = 'pg_catalog'
ORDER BY tablename DESC
LIMIT 4;

-- Multi-column: sort by schema, then by name within schema
SELECT schemaname, tablename
FROM pg_tables
ORDER BY schemaname, tablename
LIMIT 5;

-- NULLs FIRST / LAST: default for ASC is NULLS LAST, for DESC is NULLS FIRST
SELECT * FROM (VALUES (1), (NULL), (2)) AS t(n) ORDER BY n NULLS FIRST;

-- Sort by an expression
SELECT tablename, length(tablename) AS len
FROM pg_tables
WHERE schemaname = 'pg_catalog'
ORDER BY length(tablename) DESC
LIMIT 3;

ORDER BY tablename 按升序排序，ASC 是默认方向；DESC 则反过来。当两行的首个排序键相同时，就用第二个排序键打破并列，以此类推。你可以按任意表达式排序，包括未出现在选择列表中的表达式，也可以按输出位置排序（ORDER BY 1, 2）；不过，使用列名要好读得多。

空值的处理值得留意：ASC 把空值排在最后，DESC 则排在最前。用 NULLS FIRST 或 NULLS LAST 可以改变这一默认行为。如果应用无论按哪个方向排序，都希望空值放在最后，就应明确写出来；依赖默认行为，日后有人将 ASC 改为 DESC 时就容易出错。

运行示例

运行方式：

$ psql -f source/order-by.sql postgres
 tablename
--------------
 pg_aggregate
 pg_am
 pg_amop
 pg_amproc
(4 rows)

   tablename
-----------------
 pg_user_mapping
 pg_type
 pg_ts_template
 pg_ts_parser
(4 rows)
 ...

常见陷阱

• 文本排序的结果可能出乎意料：文本排序遵循数据库的排序规则（例如 en_US.UTF-8），它可能不区分大小写，也可能对重音字符做特殊处理。要按字节顺序排序，可用 ORDER BY col COLLATE "C"。
• 使用 LIMIT 时忘记 ORDER BY：没有 ORDER BY 的 LIMIT 10 返回的 10 行只是某些行，未必是最前或最小的那些。没有确定的顺序，分页就没有意义。
• 首要排序键相同的两行可以按任意顺序出现。将主键作为最后一个排序键来打破并列，就能得到完全确定的结果（ORDER BY created_at DESC, id DESC）。
• 对大型结果集排序可能代价很高。如果索引顺序与所需顺序一致，PostgreSQL 可以用索引避免排序。

下载

SQL 文件： order-by.sql · 全部示例文件 · 准备工作

6 从表中查询数据

现在 fruits 已经有了数据，可以开始读取这些行了。SELECT * 返回所有列；列出具体列名，则只返回这些列，并按列名的书写顺序排列。除非加上 ORDER BY，否则结果没有确定的行序；即使今天看起来有序，明天也未必如此。

本章要点

• 用 SELECT * FROM table 读取所有列
• 选择特定列，并控制它们在结果中的顺序
• 将 SELECT 与 WHERE、ORDER BY 组合使用
• 为什么 SELECT * 适合临时查询，却在应用代码中存在风险
• 结果列的名称，以及如何修改它们

示例

-- All columns. The order matches CREATE TABLE.
SELECT * FROM fruits ORDER BY id;

-- Specific columns, in the order you list them
SELECT name, id FROM fruits ORDER BY id;

-- Renaming output columns
SELECT id AS fruit_id, name AS fruit_name FROM fruits ORDER BY id LIMIT 3;

-- Combining a filter and an order
SELECT id, name
FROM fruits
WHERE id BETWEEN 2 AND 4
ORDER BY name;

-- Counting rows
SELECT count(*) AS total FROM fruits;

先运行 create-table.sql 和 insert.sql，再运行本章示例。SELECT * 按物理列顺序返回所有列。明确列出列名，就能完全控制结果的结构；当数据要交给按位置映射列的应用代码时，这一点尤其重要。AS 只修改输出中的列名，不会改变表中的任何内容。

* 和操作符两侧的空格可以省略；SQL 解析器对空白字符相当宽容。查询分成多行书写很常见，列数超过两列时也推荐这样做。

运行示例

运行方式：

$ psql -f source/select-from-table.sql postgres
 id  |   name
-----+-----------
   1 | apple
   2 | banana
   3 | cherry
   4 | date
   5 | elderberry
 101 | fruit_1
 102 | fruit_2
 103 | fruit_3
(8 rows)

    name    | id
------------+-----
 apple      |   1
 banana     |   2
 ...

常见陷阱

• SELECT * FROM big_table; 会通过网络取回每一行的每一列，既慢又浪费资源。请列出实际需要的列。
• 输出列名并不保证唯一。SELECT id, id FROM fruits 的两列都叫 id，应用使用的驱动可能无法妥善处理这种情况。
• 在应用代码中，应优先明确指定列名，而不是使用 SELECT *。当表结构中新增一列时，SELECT * 的结果结构就会改变，可能使依赖特定列顺序或列数的代码出错。

下载

SQL 文件： select-from-table.sql · 全部示例文件 · 准备工作

7 LIMIT 与 OFFSET

LIMIT n 至多返回 n 行。OFFSET m 跳过结果中的前 m 行。两者结合，就能实现传统的按页码分页：每页 p 行的第 k 页写作 LIMIT p OFFSET (k - 1) * p。没有 ORDER BY，“前”就没有意义；使用 LIMIT/OFFSET 时，一定要明确指定顺序。

本章要点

• 用 LIMIT 限制结果大小
• 用 OFFSET 跳过行
• 分页方式及其代价
• 为什么偏移量分页在大表上难以扩展
• 更好的替代方式：键集分页（也称游标分页）

示例

-- First two rows, ordered by id
SELECT * FROM fruits ORDER BY id LIMIT 2;

-- Skip one, then take two: rows 2 and 3
SELECT * FROM fruits ORDER BY id LIMIT 2 OFFSET 1;

-- Page 2 of 3 per page
SELECT * FROM fruits ORDER BY id LIMIT 3 OFFSET 3;

-- Keyset pagination: rows with id > last_seen_id
SELECT * FROM fruits WHERE id > 2 ORDER BY id LIMIT 2;

LIMIT 2 OFFSET 1 可以读作“跳过一行，再取两行”。在内部，PostgreSQL 仍然必须计算直到 OFFSET + LIMIT 为止的所有行，再丢弃偏移量对应的那些行。如果页码从 1 开始，每页 50 行，获取第 10,000 页就意味着要处理 500,000 行。偏移量分页用于前几页还不错，但用于深度分页就很糟糕。

键集分页（也称游标分页或 seek 分页）使用上次看到的值作为过滤条件：WHERE id > $last_id ORDER BY id LIMIT 50。无论已经翻到多后面的页，查询都只访问接下来的 50 行。这需要有索引的列（通常是主键，或 created_at, id），但也是生产系统中的标准做法。

运行示例

运行方式（需要前面章节的 fruits 表）：

$ psql -f source/limit-offset.sql postgres
 id |  name
----+--------
  1 | apple
  2 | banana
(2 rows)

 id |  name
----+--------
  2 | banana
  3 | cherry
(2 rows)
 ...

常见陷阱

• 没有 ORDER BY 的 LIMIT 10 返回的十行只是某些行，每次执行选中的行还可能不同。一定要排序。
• 排序键相同的两行可能在不同页之间交换位置。增加一个用于打破并列的排序键（ORDER BY created_at DESC, id DESC），让分页保持稳定。
• 很大的偏移量会很慢。对于“无限滚动”信息流的第 1,000 页，应改用键集分页。
• OFFSET 也会出现在 SELECT ... FOR UPDATE 这类用法中，与行锁配合使用时可能产生问题；如果需要分批处理，基于主键的键集分页同样是更安全的方式。

下载

SQL 文件： limit-offset.sql · 全部示例文件 · 准备工作

8 DISTINCT

DISTINCT 去除结果集中的重复行。去重时比较的是 SELECT 输出的整行：DISTINCT a, b 的含义是“(a, b) 的非重复组合”，而不是“非重复的 a，再随便取个 b”。PostgreSQL 还提供了它更强大的兄弟 DISTINCT ON (expr)，为 expr 的每个非重复值保留一行，很适合“每个 Y 对应的最新 X”这类查询。

本章要点

• 用 DISTINCT 去除重复项
• DISTINCT 如何作用于 SELECT 选择列表中的所有列
• PostgreSQL 特有的 DISTINCT ON (expr)
• DISTINCT 如何处理 NULL
• 何时应该改用 GROUP BY

示例

-- Unique values in a column
SELECT DISTINCT name FROM fruits ORDER BY name LIMIT 5;

-- Distinct combinations of multiple columns
SELECT DISTINCT id, name FROM fruits ORDER BY id LIMIT 3;

-- DISTINCT ON: keep one row per distinct expression value.
-- Here: one row per first letter of the name, picking the smallest id.
SELECT DISTINCT ON (substring(name, 1, 1)) substring(name, 1, 1) AS letter, id, name
FROM fruits
ORDER BY substring(name, 1, 1), id;

-- DISTINCT treats NULL as a single value
SELECT DISTINCT n FROM (VALUES (1), (NULL), (NULL), (2)) AS t(n) ORDER BY n;

在我们的 fruits 表中，每个名称本来就不重复，因此 DISTINCT name 与 SELECT name 返回相同的行。有多列时，只有列出的值全部相同，DISTINCT 才会把两行视为重复行。

要查询“每组的第一行”，DISTINCT ON (substring(name, 1, 1)) 是 PostgreSQL 的拿手好戏。ON (...) 中的表达式定义分组，ORDER BY 决定各组保留哪一行，也就是排序后的第一行。关键在于，ON 中的表达式必须出现在 ORDER BY 的开头，否则 PostgreSQL 无法保证你说的“第一行”是哪一行。

处理空值时，DISTINCT 把两个 NULL 视为相等。考虑到 NULL = NULL 的结果是未知，这有些违反直觉，但 SQL 标准就是这样规定的。

运行示例

运行方式：

$ psql -f source/distinct.sql postgres
    name
------------
 apple
 banana
 cherry
 date
 elderberry
(5 rows)
 ...

常见陷阱

• SELECT DISTINCT a, b FROM t 与 SELECT DISTINCT a, (b) FROM t 含义相同，都是对 (a, b) 组合去重。给 b 加上括号，并不会让去重只作用于 a。
• DISTINCT 与 ORDER BY 会相互影响：ORDER BY 中的任何列如果不在 DISTINCT 列表中，就会报错（不能按一个在同一去重分组内可能有多个值的列来排序）。
• DISTINCT ON 不是标准 SQL。需要可移植性的代码应改用窗口函数（ROW_NUMBER() OVER (PARTITION BY ...)），后面的章节会介绍。
• 在大表上使用 SELECT DISTINCT 必须做排序或哈希；如果只想知道“是否有重复项”，可用 GROUP BY ... HAVING COUNT(*) > 1 或聚合。

下载

SQL 文件： distinct.sql · 全部示例文件 · 准备工作

9 空值（NULL）

NULL 是 SQL 中表示未知或缺失的标记。它不是零，不是空字符串，也不等于任何值，甚至不等于另一个 NULL。这种三值逻辑（真、假、未知）引发的 SQL 错误比其他任何特性都多，不过一旦熟悉，规则其实很简单。

本章要点

• NULL 表示什么
• 用 IS NULL / IS NOT NULL 检查 NULL
• NULL 如何在表达式和比较中传播
• NULL 在 AND/OR 中的行为（三值逻辑）
• 用 COALESCE 和 NULLIF 替换 NULL（译注：此处原文概括不够准确；前者用非空值替换空值，后者可将指定值转为空值。）

示例

-- NULL is missing/unknown
SELECT 1 AS a, NULL AS b;

-- = NULL is NULL (not true), so this returns 0 rows
SELECT * FROM (VALUES (1, NULL)) AS t(id, name) WHERE name = NULL;

-- IS NULL / IS NOT NULL are the correct tests
SELECT * FROM (VALUES (1, NULL)) AS t(id, name) WHERE name IS NULL;
SELECT * FROM (VALUES (1, 'x')) AS t(id, name) WHERE name IS NOT NULL;

-- Any arithmetic with NULL is NULL
SELECT 1 + NULL AS one_plus_null, 'a' || NULL AS concat_null;

-- COALESCE returns the first non-null argument
SELECT COALESCE(NULL, NULL, 'default') AS coalesced;

-- NULLIF returns NULL if the two args are equal, else the first
SELECT NULLIF(0, 0) AS hides_zero, NULLIF(5, 0) AS keeps_five;

-- Three-valued logic
SELECT (TRUE AND NULL) AS t_and_n, (TRUE OR NULL) AS t_or_n, (FALSE AND NULL) AS f_and_n;

= NULL 不起作用，是因为比较结果是 NULL，不是 TRUE，所以 WHERE 会排除这一行。<>、<、>、IN、BETWEEN 等也是如此；几乎所有操作符都会在任一操作数为 NULL 时返回 NULL。例外是 IS NULL、IS NOT NULL、IS DISTINCT FROM 和 IS NOT DISTINCT FROM（后两者分别是能正确处理 NULL 的 <> 和 =）。

COALESCE(a, b, c) 返回第一个不是 NULL 的参数。这是提供默认值的标准做法，例如 COALESCE(nickname, first_name, 'Anonymous')。NULLIF(a, b) 在 a = b 时返回 NULL；如果想把用作标记的特殊值转回 NULL，它就很方便（例如 NULLIF(comment, '')）。

三值逻辑表很短，值得记住：TRUE OR NULL 是 TRUE（至少有一侧为真），但 TRUE AND NULL 是 NULL（不知道另一侧的值）。FALSE AND NULL 是 FALSE（有一侧为假就够了）。FALSE OR NULL 是 NULL。

运行示例

运行方式：

$ psql -f source/nulls.sql postgres
 a | b
---+---
 1 |
(1 row)
 ...

常见陷阱

• 忘记聚合会跳过 NULL：SUM、AVG、MIN、MAX、COUNT(col) 都忽略 NULL 输入。COUNT(*) 则统计每一行，包括含有 NULL 的行。
• 只要子查询返回一个 NULL，NOT IN (subquery) 就会得到空结果，因为 x <> NULL 的结果是 NULL。优先使用 NOT EXISTS。
• NULL <> NULL 是未知（NULL），不是真。DISTINCT 把 NULL 归为一组；IS DISTINCT FROM 提供的比较则总是返回真或假。
• 默认排序：ORDER BY col ASC 把 NULL 排在最后，DESC 把它们排在最前。明确指定 NULLS FIRST/NULLS LAST，让结果符合预期。

下载

SQL 文件： nulls.sql · 全部示例文件 · 准备工作

10 表达式

表达式就是 PostgreSQL 能够求值为单个值的任何内容：字面量、列引用、算术运算、函数调用、CASE，甚至另一个返回单个值的 SELECT。表达式几乎无处不在：选择列表、WHERE、ORDER BY、GROUP BY 和索引定义中都能使用。掌握表达式，也就掌握了 SQL。

本章要点

• 算术、字符串和布尔表达式
• 在表达式中调用内置函数
• 在 SELECT、WHERE 和 ORDER BY 中使用表达式
• 用 :: 和 CAST(... AS ...) 做类型转换
• 用 AS 为表达式结果命名

示例

-- Arithmetic and concatenation
SELECT 10 + 2 AS sum, 10 * 2 AS product, 10 / 4 AS int_div, 10 / 4.0 AS real_div;
SELECT 'Hello ' || 'world' AS greeting;
SELECT 'fruit_' || 1 AS labeled;  -- numbers are coerced to text for ||

-- Function calls in expressions
SELECT upper(name), length(name), name || ' (#' || id || ')' AS label
FROM fruits ORDER BY id LIMIT 3;

-- Expression in WHERE
SELECT * FROM fruits WHERE id * 2 > 4 ORDER BY id LIMIT 3;

-- Expression in ORDER BY
SELECT id, name FROM fruits ORDER BY length(name) DESC LIMIT 3;

-- Casting
SELECT '42'::integer AS to_int, 42::text AS to_text, '2024-01-15'::date AS to_date;
SELECT CAST(3.7 AS integer) AS rounded_value;  -- SQL-standard cast syntax

|| 操作符用于拼接字符串。在 PostgreSQL 中，如果任一操作数不是 text 类型，就会将其转换为 text（所以 'id_' || 5 可以运行）。+、-、*、/、% 是常见的算术操作符；记住，整数除法会向零截断。

PostgreSQL 的类型转换有两种写法。value::type 是 PostgreSQL 特有的简写，也是常用写法。CAST(value AS type) 属于 SQL 标准，表达式复杂时读起来更清楚。两种写法最终表达的是同一种类型转换。

WHERE 和 ORDER BY 中的表达式会对每个候选行求值，因此在这里调用函数可能会妨碍使用索引。WHERE lower(email) = 'a@b.com' 无法使用 email 上的普通索引，但如果定义了 CREATE INDEX ON users (lower(email)) 这样的表达式索引，就可以使用它。

运行示例

运行方式：

$ psql -f source/expressions.sql postgres
 sum | product | int_div | real_div
-----+---------+---------+----------
  12 |      20 |       2 |      2.5
(1 row)

   greeting
-------------
 Hello world
(1 row)
 ...

常见陷阱

• 整数除法返回 0：1 / 2 的结果是 0。可以改用非整数类型：1.0 / 2、1::numeric / 2，或使用 numeric 类型的列。
• 对无法解析的字符串做类型转换，例如 'abc'::integer，会报错。与 MySQL 相比，PostgreSQL 对类型的要求更严格。
• 如果表达式在 CASE 的不同分支中返回不同类型，就会引发类型不匹配错误；应转换为共同类型。（译注：不同类型并不一定报错；若能按类型解析规则隐式转换为共同类型，表达式仍可成立。）
• 操作符具有优先级：1 + 2 * 3 的结果是 7。拿不准时就加括号。

下载

SQL 文件： expressions.sql · 全部示例文件 · 准备工作

11 数值类型

PostgreSQL 提供三大类数值类型：整数、精确小数和浮点数。每一类都有自己的适用场景。整数适合计数器和 ID，速度快、占用空间小。numeric（也写作 decimal）提供精确运算，这对金额至关重要。浮点数（real、double precision）速度快、占用空间小，但可能产生微小的舍入误差，不应用于货币。

本章要点

• 整数类型族：smallint、integer、bigint
• 精确小数：numeric(p, s) 和不带约束的 numeric
• 浮点数：real、double precision
• 精度和标度如何发挥作用
• 何时选择哪一种类型

示例

-- Integer family
SELECT 1::smallint AS s, 1::integer AS i, 1000000::bigint AS b;

-- Range limits (smallint -32768..32767, integer -2.1B..2.1B, bigint very large)
SELECT pg_typeof(1) AS literal_int_type;  -- 'integer' by default

-- Exact decimal: numeric(precision, scale)
-- precision = total digits, scale = digits after the decimal point
SELECT 3.14::numeric(4, 2) AS pi, 12345.67::numeric(7, 2) AS money;

-- Unconstrained numeric is exact within PostgreSQL's implementation limits
SELECT '0.1'::numeric + '0.2'::numeric AS exact;  -- 0.3, exactly

-- Floats are approximate but fast
SELECT 0.1::double precision + 0.2::double precision AS approximate;  -- 0.30000000000000004

-- Useful generators
SELECT generate_series(1, 5) AS n;

integer 是默认的整数类型，1 解析后就是这种类型。当数值可能超过 20 亿时（例如大型表中的行数、自纪元以来的毫秒数等），应改用 bigint。smallint 很少值得使用：它节省的存储空间很少，而超出其范围的值会报错。

numeric(p, s) 强制总位数最多为 p 位，其中 s 位在小数点后。numeric(10, 2) 存储范围从 -99,999,999.99 到 99,999,999.99。不带约束的 numeric（没有 p、s）在 PostgreSQL 的实现限制内存储精确的小数值，速度比定长整数慢，但结果精确。它适合金额、实验室测量值，以及任何不能接受舍入误差的场景。

real（4 字节，约 6 位有效数字）和 double precision（8 字节，约 15 位有效数字）都是 IEEE 754 浮点数。它们速度快、占用空间小，但 0.1 + 0.2 不会精确等于 0.3。如果可以容忍不精确，它们适合物理测量、机器学习特征和统计聚合。

运行示例

运行方式：

$ psql -f source/numeric-types.sql postgres
 s | i |    b
---+---+---------
 1 | 1 | 1000000
(1 row)
 ...
 exact
-------
 0.3
(1 row)

   approximate
------------------
 0.30000000000000004
(1 row)

常见陷阱

• 用 double precision 存储金额——迟早会在分币舍入上出问题。请使用 numeric(p, 2)（或不带范围限制的 numeric），让数据库执行精确运算。
• 在大型表中为 ID 列选择 integer——可能触及 21 亿的上限。对于预计会增长的主键，默认使用 bigint。
• 对含有非数字字符的字符串做类型转换：'$5'::numeric 会报错。先去除这些字符，再做类型转换。
• 整数相除可能让你感到意外：7 / 2 的结果是 3，而不是 3.5。可以做类型转换：7::numeric / 2。

下载

SQL 文件：numeric-types.sql · 全部示例文件 · 准备工作

12 文本类型

PostgreSQL 有三种字符类型：text（无界）、varchar(n)（可变长度，最多 n 个字符）和 char(n)（固定 n 个字符，以空格填充）。对于几乎所有使用场景，text 都是正确的选择。PostgreSQL 的 text 和 varchar 在性能特征上完全相同——与 MySQL 不同，除非希望让数据库强制执行最大长度，否则限制长度没有任何好处。

本章要点

• 三种文本类型，以及各自适用的场景
• 为什么 text 是 PostgreSQL 中的默认选择
• 长度函数：length、char_length、octet_length
• 排序规则基础——文本如何排序和比较
• 编码：PostgreSQL 使用数据库的编码存储文本（通常是 UTF-8）

示例

-- text: unbounded, recommended default
SELECT 'hello'::text AS t, length('hello'::text) AS chars;

-- varchar(n): same as text but enforces max length
SELECT 'hi'::varchar(10) AS v, length('hi'::varchar(10)) AS chars;

-- char(n): fixed length, padded with spaces
SELECT 'ab'::char(5) AS c, length('ab'::char(5)) AS chars, '|' || 'ab'::char(5) || '|' AS padded;

-- char_length vs octet_length on multibyte text
SELECT char_length('café') AS chars, octet_length('café') AS bytes;

-- A taste of collation: byte-order sort
SELECT * FROM (VALUES ('apple'), ('Banana'), ('cherry')) AS t(name)
ORDER BY name COLLATE "C";

text 是最常用的文本类型。它可以存储任意长度的字符串，也是大多数应用列应该使用的类型。在内部，PostgreSQL 以相同方式存储 text 和 varchar，对大值使用 TOAST（超长属性存储技术），从而让主表保持紧凑。

varchar(n) 就是在 text 上增加了长度检查。只有在确实有理由限制长度时才使用它（例如需要与某个要求使用这种类型的系统集成）。不带长度的 varchar 与 text 完全相同。

char(n) 是历史遗留的定长类型。它在存储时用空格将值填充到 n 的长度，并在某些操作中去掉这些空格，因此会带来意外。除非有非常具体的理由（例如需要与要求定长编码的系统对接），否则应避免使用 char(n)。

运行示例

运行方式：

$ psql -f source/text-types.sql postgres
   t   | chars
-------+-------
 hello |     5
(1 row)

 v  | chars
----+-------
 hi |     2
(1 row)

   c   | chars |  padded
-------+-------+----------
 ab    |     2 | |ab|
(1 row)
 ...

常见陷阱

• 出于习惯选择 varchar(255)：在 PostgreSQL 中它没有实际用处。请使用 text。如果应用确实需要限制长度，就在应用中或通过 CHECK 约束来强制执行。
• char(n) 的填充会悄悄改变你的值。'foo'::char(10) 会存储为 'foo '。
• 多字节字符：length('café') 返回 4（字符数），而 octet_length 返回 5（UTF-8 中的字节数）。根据你要回答的问题选择合适的函数。
• 排序时的比较取决于数据库的 LC_COLLATE。要按字节顺序排序（通常更快，也更可预测），请使用 COLLATE "C"。

下载

SQL 文件：text-types.sql · 全部示例文件 · 准备工作

13 布尔值与日期

PostgreSQL 的 boolean 类型存储 true、false 或 NULL。日期和时间有一组常用类型：date 表示日历日期，time 表示一天中的时间，timestamp 表示不带时区的日期和时间，而 timestamptz（timestamp with time zone）表示一个按会话时区显示的时间点（不会保留原始时区/偏移量）。对于几乎所有应用场景，应该使用 timestamptz。

本章要点

• boolean 类型及其多种输入形式（true、't'、'yes'、1）
• 四种核心日期/时间类型及各自适用的场景
• 为什么优先选择 timestamptz 而不是 timestamp
• current_date、current_timestamp、now()、localtime
• 使用 interval 进行日期运算

示例

-- Boolean: true, false, NULL
SELECT true AS t, false AS f, (1 = 1) AS same;

-- Boolean accepts many input forms
SELECT 'yes'::boolean AS yes, 'n'::boolean AS n, '1'::boolean AS one;

-- Dates and times
SELECT current_date AS today,
       current_timestamp AS now_tz,
       localtimestamp AS now_local,
       current_time AS time_now;

-- Casting date/time literals
SELECT '2024-01-15'::date AS d,
       '2024-01-15 10:30:00'::timestamp AS ts,
       '2024-01-15 10:30:00+00'::timestamptz AS ts_tz;

-- Arithmetic with interval
SELECT current_date + 7 AS in_a_week,
       current_timestamp - interval '1 hour' AS an_hour_ago,
       date '2024-12-31' - date '2024-01-01' AS days_between;

-- Extracting parts
SELECT extract(year FROM current_date) AS year,
       extract(dow  FROM current_date) AS day_of_week;

boolean 类型接受多种输入字符串：true、false、t、f、yes、no、y、n、1、0、on、off。在 psql 中，输出总是显示为 t 或 f。比较（=、<> 等）会产生布尔值；可以把比较结果存储在 boolean 列中。

timestamp without time zone（即 timestamp）存储挂钟时间值，不记录它在地球上的哪个位置。如果插入 2024-01-15 10:00:00，无论由谁读取，取回的都完全是这个值。timestamp with time zone（timestamptz）在内部以 UTC 存储同一个时间点，并按会话时区显示。处于不同时区的两个会话看到的 timestamptz 会是不同的挂钟时间，但底层的时间点相同。

对于几乎所有“这个事件是什么时候发生的？”的问题——下单、写入日志、创建用户——timestamptz 都是正确答案。普通 timestamp 适用于“食谱要求在当地时间上午 10 点烘焙，用户在哪个时区就按哪个当地时间执行”之类的场景，这种情况很少见。

运行示例

运行方式：

$ psql -f source/boolean-and-dates.sql postgres
 t | f | same
---+---+------
 t | f | t
(1 row)
 ...
   today
------------
 2024-xx-xx
(1 row)
 ...

常见陷阱

• 把事件时间存为 timestamp 而不是 timestamptz：一年后，你无法判断它是 UTC、服务器本地时间还是用户本地时间。一律使用 timestamptz。
• 将 date 或 timestamp without time zone 与 timestamptz 比较时，会使用会话时区来解释不带时区的值。重要时务必明确指定。
• now() 返回的是事务开始时刻，而不是当前挂钟时间。在一个长事务中，重复调用会返回相同值。要获取真正的挂钟时间，请使用 clock_timestamp()。
• interval '1 month' 加上日期会得到下个月的时间戳，有时会带来意外（January 31 + 1 month = February 28/29）。

下载

SQL 文件：boolean-and-dates.sql · 全部示例文件 · 准备工作

14 UUID 与 JSONB

PostgreSQL 中两种最受欢迎的非基础类型：uuid 用于不透明、全局唯一的标识符，jsonb 用于可建立索引并查询的二进制编码 JSON。UUID 是顺序整数 ID 的标准替代方案，适用于需要 ID 不可猜测、可在客户端生成或跨系统全局唯一的场景。对于半结构化数据，jsonb 让 PostgreSQL 足以与文档存储系统竞争。

本章要点

• 使用 gen_random_uuid() 生成 UUID
• 使用 jsonb 存储并查询 JSON
• ->、->>、#>、#>> 操作符及其返回值
• @> 包含关系操作符（以及它如何使用 GIN 索引）
• json 与 jsonb 的区别

示例

-- UUID generation (built-in from PostgreSQL 13)
SELECT gen_random_uuid() AS id;

-- jsonb literal
SELECT '{"name": "Alice", "age": 30, "tags": ["admin", "beta"]}'::jsonb AS j;

-- -> returns jsonb (preserves type); ->> returns text
SELECT '{"name": "Alice"}'::jsonb -> 'name'  AS as_jsonb,
       '{"name": "Alice"}'::jsonb ->> 'name' AS as_text;

-- Array indexing (0-based)
SELECT '["a", "b", "c"]'::jsonb -> 1 AS second;

-- Path operators: #> returns jsonb, #>> returns text
SELECT '{"user": {"address": {"city": "Paris"}}}'::jsonb #>> '{user, address, city}' AS city;

-- Containment: @> checks if the right side is contained in the left
SELECT '{"name": "Alice", "age": 30}'::jsonb @> '{"name": "Alice"}'::jsonb AS contains_alice;

-- Querying jsonb columns in a real table
DROP TABLE IF EXISTS docs_example;
CREATE TABLE docs_example (id integer PRIMARY KEY, data jsonb);
INSERT INTO docs_example VALUES
  (1, '{"name": "Alice", "tags": ["admin"]}'),
  (2, '{"name": "Bob",   "tags": ["beta"]}');

SELECT id, data ->> 'name' AS name
FROM docs_example
WHERE data @> '{"tags": ["admin"]}';

gen_random_uuid() 会生成随机的版本 4 UUID。从 PostgreSQL 13 开始它就是内置函数，不需要 pgcrypto 扩展。PostgreSQL 17 没有内置的 UUIDv7 生成器；如果需要按时间排序的 UUID，请使用应用程序/库或扩展。PostgreSQL 18 新增 uuidv7()。bigint 标识列也是一种选择。

对于 JSON，优先使用 jsonb 而不是 json。json 会原样存储输入文本（包括空白和键顺序）；jsonb 会解析、规范化并以二进制形式存储。通常对 jsonb 建立索引并进行高效查询，几乎所有应用都使用它。只有在需要逐字节保留输入（这种情况很少见）时，才使用普通 json。

-> 操作符提取并返回 jsonb。->> 提取并返回 text（去掉 JSON 引号）。#> 和 #>> 使用路径（由键/下标组成的数组）。@>（“包含”）测试左值是否包含右值——它是 JSON 查询的基础。

运行示例

运行方式：

$ psql -f source/uuid-jsonb.sql postgres
                  id
--------------------------------------
 xxxxxxxx-xxxx-4xxx-xxxx-xxxxxxxxxxxx
(1 row)
 ...
 contains_alice
----------------
 t
(1 row)
 ...

常见陷阱

• 将 data -> 'name' 与字符串比较：data -> 'name' = '"Alice"' 可以工作（注意带 JSON 引号的字符串），但通常应该使用 data ->> 'name' = 'Alice'。
• 忘记建立索引：对没有 GIN 索引的 jsonb 列进行过滤会执行全表扫描。CREATE INDEX ON docs_example USING gin (data); 让 @> 及类似查询能够使用索引。
• 就地修改 jsonb：UPDATE t SET data = data || '{"k": "v"}'::jsonb 会添加或替换顶层键；jsonb_set 可以修改嵌套路径。
• jsonb 内部的模式漂移：它很灵活，但你仍需知道其中有什么。记录预期结构，并在应用层或使用 CHECK 约束进行验证。

下载

SQL 文件：uuid-jsonb.sql · 全部示例文件 · 准备工作

15 数组与 ENUM

另外两种值得尽早了解的 PostgreSQL 专用类型。数组允许任意列存放同一类型值组成的有序列表——有时，一对多关系的范围非常明确，单独建表就有些小题大做（例如文章上的标签、用户的角色名）。ENUM 类型为列提供一组固定的命名值，由数据库强制约束，并以紧凑形式存储。二者都不是标准类型，但支持良好，在实际 Postgres 应用中广泛使用。

本章要点

• 声明和查询数组列
• 数组字面量、数组下标（从 1 开始）及实用函数
• 数组上的 ANY / ALL 操作符
• 定义和使用 ENUM 类型
• 何时适合使用数组/枚举，以及何时单独建表更好

示例

-- Arrays: any type can be turned into an array by appending []
DROP TABLE IF EXISTS posts_example;
CREATE TABLE posts_example (
  id   integer PRIMARY KEY,
  title text,
  tags  text[]                  -- array of text
);

INSERT INTO posts_example VALUES
  (1, 'Hello', ARRAY['intro', 'meta']),
  (2, 'SQL tips', '{tips, postgres}'),   -- shorthand literal
  (3, 'Untagged', ARRAY[]::text[]);

-- Indexing (1-based!) and length
SELECT id, title, tags[1] AS first_tag, array_length(tags, 1) AS tag_count
FROM posts_example
ORDER BY id;

-- ANY: row matches if ANY element of the array equals 'tips'
SELECT id, title FROM posts_example WHERE 'tips' = ANY(tags);

-- Array containment: @> "left contains all elements of right"
SELECT id, title FROM posts_example WHERE tags @> ARRAY['postgres'];

-- ENUM: defined type with a fixed set of allowed values
DROP TABLE IF EXISTS tickets_example;
DROP TYPE  IF EXISTS ticket_status;
CREATE TYPE ticket_status AS ENUM ('open', 'in_progress', 'closed');

CREATE TABLE tickets_example (
  id     integer PRIMARY KEY,
  title  text,
  status ticket_status NOT NULL DEFAULT 'open'
);
INSERT INTO tickets_example (id, title, status) VALUES (1, 'Bug 1', 'open'), (2, 'Bug 2', 'closed');

SELECT * FROM tickets_example ORDER BY id;

PostgreSQL 中的数组是一等公民：可以为它们建立索引，在 WHERE 子句中使用，用 ARRAY[] 构造，用 unnest() 将其展开成行，并用 @>、<@ 查询包含关系，用 && 查询重叠。它们以紧凑形式内联存储（非常大的数组则使用超长属性存储技术（TOAST））。

最重要的一个特殊之处是：数组下标默认从 1 开始。这里的 tags[1] 是数组中的第一个元素，不过 PostgreSQL 也支持显式指定下界。'{a, b, c}' 这种字面量形式是字符串形式的字面量简写；ARRAY['a', 'b', 'c'] 是构造器——两者生成相同的值。

ENUM 类型非常适合这样的场景：允许值集合已知且规模较小，并且应由数据库强制约束（'open' | 'in_progress' | 'closed'、'small' | 'medium' | 'large'）。无论标签长度如何，它们都以 4 字节存储，并且按声明顺序而不是字母顺序排序——对于状态工作流来说很有用，因为 'open' < 'in_progress' < 'closed' 正是自然顺序。

运行示例

运行方式：

$ psql -f source/arrays-and-enum.sql postgres
DROP TABLE
CREATE TABLE
INSERT 0 3
 id |  title   | first_tag | tag_count
----+----------+-----------+-----------
  1 | Hello    | intro     |         2
  2 | SQL tips | tips      |         2
  3 | Untagged |           |
(3 rows)
 ...

常见陷阱

• 使用其他语言的开发者很容易在从 1 开始的数组下标上栽跟头。tags[0] 的结果是 NULL，而不是报错。
• 修改 ENUM（添加一个值）需要使用 ALTER TYPE；不重建类型，删除一个值基本上不可能。对于经常变化的集合，优先使用通过外键连接的小型查找表。
• 数组看起来很适合“我只需要一个列表”的场景——但只要你需要单独查询、连接、排序或分别约束其中的元素，另建一张带外键的表几乎总是更清晰。只有当元素属于该行、而不是独立实体时，才使用数组。
• tag = 'x' 在数组列上不起作用——使用 'x' = ANY(tag) 或 tag @> ARRAY['x']。

下载

SQL 文件：arrays-and-enum.sql · 全部示例文件 · 准备工作

16 CREATE TABLE

PostgreSQL 通过表存储数据。CREATE TABLE 通过列出列及其数据类型来定义新表。默认值、约束、索引和外键都可以在创建时添加，但最小的表只需要一个名称和列清单。本课保持简洁；约束会在专门的课程中介绍。

本章要点

• 使用 CREATE TABLE 定义表
• 为每列选择数据类型（integer、text 等）
• 使用 DROP TABLE IF EXISTS 使建表脚本幂等
• 检查 public 模式中的表
• TEMP TABLE、普通表和 UNLOGGED TABLE 的区别

示例

-- Drop the table if it exists so the script is idempotent
DROP TABLE IF EXISTS fruits;

-- A minimal table: id and name
CREATE TABLE fruits (
  id   integer,
  name text
);

-- Confirm it exists in the current schema
SELECT schemaname, tablename
FROM pg_tables
WHERE tablename = 'fruits';

-- See the columns and their types
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'fruits'
ORDER BY ordinal_position;

fruits 有两列：id（32 位有符号整数）和 name（变长文本）。CREATE TABLE 中列的顺序，就是行的物理存储顺序——为了提高宽表的存储效率，应将定长列放在一起。对于小表，这并不重要。

DROP TABLE IF EXISTS 只在表存在时删除表；如果没有 IF EXISTS，该语句在全新的数据库中就会报错。将它与 CREATE TABLE 结合后，在迭代数据库模式的过程中可以安全地反复运行脚本。当然，在生产环境中不要盲目删除表——请使用迁移。

有几种 CREATE TABLE 的变体值得了解：

• CREATE TEMP TABLE（或 CREATE TEMPORARY TABLE）创建的表只在当前会话中存在，并会自动删除。
• CREATE UNLOGGED TABLE 不写入预写式日志——写入更快，但服务器崩溃时内容会被清空。适合存放可重建的派生数据或暂存数据。
• CREATE TABLE ... (LIKE other_table INCLUDING ALL) 会复制另一个表的模式。
• CREATE TABLE ... AS SELECT ... 会根据查询创建并填充一个表（称为 CTAS）。

运行示例

运行方式：

$ psql -f source/create-table.sql postgres
DROP TABLE
CREATE TABLE
 schemaname | tablename
------------+-----------
 public     | fruits
(1 row)

 column_name | data_type | is_nullable
-------------+-----------+-------------
 id          | integer   | YES
 name        | text      | YES
(2 rows)

常见陷阱

• 不要因为在 MySQL 中学到 varchar(255) 就选择它：在 PostgreSQL 中，text 和 varchar(n) 的性能相同。除非你明确需要长度上限，否则使用 text。
• 忘记在本不应为空的列上添加 NOT NULL——以后可以再加，但从一开始就正确设置更容易。
• 将列命名为 user、order、select——这些都是保留字。可以改名（user_id、placed_at），也可以加引号（"user"），但这样会一直带来麻烦。

下载

SQL 文件：create-table.sql · 全部示例文件 · 准备工作

17 列类型与约束

实际的表会为每一列选择一个类型，通常还会添加约束——数据库对每一行强制执行的规则。最常见的约束包括 NOT NULL（列必须有值）、DEFAULT（未提供值时使用该值）、CHECK（必须为真的布尔表达式）、UNIQUE、PRIMARY KEY 和 REFERENCES（外键）。每种约束都会单独讲解；这里把其中几种组合到一个贴近实际的表中。

本章要点

• 在一个 CREATE TABLE 中组合多种类型和约束
• 内联 DEFAULT 和 NOT NULL
• 用于业务规则的 CHECK 约束
• 使用 timestamptz，并以 current_timestamp 作为默认值
• 在系统目录中检查约束

示例

DROP TABLE IF EXISTS products;

CREATE TABLE products (
  id          integer PRIMARY KEY,
  name        text NOT NULL,
  price       numeric(10, 2) NOT NULL CHECK (price >= 0),
  in_stock    boolean NOT NULL DEFAULT true,
  category    text DEFAULT 'misc',
  created_at  timestamptz NOT NULL DEFAULT current_timestamp,
  CONSTRAINT name_not_blank CHECK (length(trim(name)) > 0)
);

INSERT INTO products (id, name, price)              VALUES (1, 'Widget',    19.99);
INSERT INTO products (id, name, price, in_stock)    VALUES (2, 'Gadget',    49.99, false);
INSERT INTO products (id, name, price, category)    VALUES (3, 'Sprocket',  4.95, 'parts');

SELECT * FROM products ORDER BY id;

-- Inspect the constraints on the table
SELECT conname, pg_get_constraintdef(oid) AS def
FROM pg_constraint
WHERE conrelid = 'products'::regclass
ORDER BY conname;

每个约束都是数据库对你的数据作出的一项承诺。NOT NULL 表示该值始终存在。DEFAULT 会在 INSERT 省略该列时提供一个值。CHECK (expr) 会拒绝 expr 为假的任何行（NULL 值会通过）。PRIMARY KEY 意味着 NOT NULL 和 UNIQUE，并会创建索引。

约束可以是内联的（位于列类型之后，如上），也可以是表级的（位于所有列之后）。复合的 PRIMARY KEY、UNIQUE 和外键约束必须使用表级语法。CHECK 在两种位置都可以引用多个列；列约束和表约束都可以用 CONSTRAINT name 命名，以便生成更清晰的错误信息。

timestamptz NOT NULL DEFAULT current_timestamp 是最常见的列定义形式之一——行的创建时间存储为 UTC，永不为空，并自动设置。

运行示例

运行方式：

$ psql -f source/column-types-and-constraints.sql postgres
DROP TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
 id |   name   | price | in_stock | category |          created_at
----+----------+-------+----------+----------+-------------------------------
  1 | Widget   | 19.99 | t        | misc     | 2024-xx-xx xx:xx:xx.xxxxxx+00
  2 | Gadget   | 49.99 | f        | misc     | 2024-xx-xx xx:xx:xx.xxxxxx+00
  3 | Sprocket |  4.95 | t        | parts    | 2024-xx-xx xx:xx:xx.xxxxxx+00
(3 rows)
 ...

常见陷阱

• 忘记在本不应为空的列上添加 NOT NULL——以后添加需要先回填数据，而且在大表上可能需要长时间持有锁。
• CHECK 在表达式为 NULL 时接受该行。若要拒绝 NULL 值，请将 NOT NULL 与 CHECK 结合使用。
• 将 now() 用作 DEFAULT：它确实有效，但在长事务中，每个默认值都是事务开始时的时间。通常这没有问题；如果确实需要每一行的挂钟时间，请使用 clock_timestamp()。
• 过去，在超大表上为新列添加 DEFAULT 和 NOT NULL 会重写整个表；现代 PostgreSQL（11+）在默认值为常量时会避免这种重写，但易变的默认值仍会触发表重写。

下载

SQL 文件：column-types-and-constraints.sql · 全部示例文件 · 准备工作

18 ALTER TABLE 与 DROP

表会不断演化。ALTER TABLE 用于修改已有表：添加或删除列，重命名列或表本身，添加或删除约束，修改列的类型，修改列的默认值。DROP TABLE 会删除表及其包含的每一行。PostgreSQL 中的模式变更具有事务性——ALTER TABLE 失败时会正常回滚，你还可以把多个 ALTER 放进 BEGIN ... COMMIT，以原子方式应用它们。

本章要点

• 添加、重命名和删除列
• 添加和删除约束
• 使用 USING 修改列的类型
• 重命名表
• 常见修改操作对锁和表重写的影响

示例

-- ADD COLUMN: new columns appear at the end
ALTER TABLE products ADD COLUMN description text;

-- ADD COLUMN with a default (cheap in PG 11+ for constant defaults)
ALTER TABLE products ADD COLUMN sku text NOT NULL DEFAULT '';

-- Rename a column
ALTER TABLE products RENAME COLUMN description TO short_desc;

-- Change a column type (use USING when a cast is needed)
ALTER TABLE products ALTER COLUMN sku TYPE varchar(40);

-- Add a constraint after the fact
ALTER TABLE products ADD CONSTRAINT sku_format CHECK (sku ~ '^[A-Z0-9-]*$');

-- Drop a column
ALTER TABLE products DROP COLUMN short_desc;

-- Rename the table itself
ALTER TABLE products RENAME TO products_old;
ALTER TABLE products_old RENAME TO products;

-- DROP TABLE removes the table and all data
-- (commented out so the rest of the lessons can still use products)
-- DROP TABLE products;

SELECT tablename FROM pg_tables WHERE tablename = 'products';

每条 ALTER TABLE 都是一个独立的事务性语句。你也可以批量执行多个操作：ALTER TABLE t ADD COLUMN a int, ADD COLUMN b text, DROP COLUMN c;——把它们放在一条语句中运行会更快，因为表只需被访问一次。

有两种操作值得从代价角度理解：

• 添加没有默认值（或默认值为常量）的列很快——PostgreSQL 只需记录新列的元数据。
• 许多列类型变更都会重写表。 ALTER COLUMN x TYPE bigint 会读取并重写每一行。在大表上，这可能耗时数小时，并在整个过程中持有排他锁。有些二进制兼容的变更可以避免重写。对于重写量大的变更，应规划如下迁移：添加新列 → 回填 → 切换；pg_repack 不能执行任意类型转换。

ALTER TABLE ... ALTER COLUMN col TYPE new_type USING expr 允许你指定如何转换现有值：ALTER COLUMN price TYPE numeric(12,2) USING price::numeric(12,2)。

运行示例

运行方式：

$ psql -f source/alter-table-and-drop.sql postgres
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
 tablename
-----------
 products
(1 row)

常见陷阱

• 向现有列添加 NOT NULL 时，要求该列中已经没有任何空值，并且会获取 ACCESS EXCLUSIVE 锁，一直持有到验证完成。先回填，再 SET NOT NULL。从 PostgreSQL 12 开始，如果存在匹配的 CHECK 约束，ALTER COLUMN x SET NOT NULL 可以更快完成。
• 删除列不会立即回收磁盘空间——该列只会被标记为无效。运行 VACUUM FULL（重量级操作）或重新创建表，才能回收空间。
• 重命名列会更新由系统目录跟踪的依赖关系，例如视图，但可能破坏应用程序 SQL 和以文本形式存储的函数体。重命名之前请检查这些用法。
• 对 ENUM 执行 ALTER TYPE 时，不能移除已有值；添加值则没有问题。

下载

SQL 文件：alter-table-and-drop.sql · 全部示例文件 · 准备工作

19 主键与唯一约束

一个主键可以唯一标识一行。每张表都应该有一个主键——它为索引、连接、外键和应用程序代码提供了稳定的行定位依据。一张表至多有一个主键（主键可以由多个列组成）。UNIQUE 是更通用的“这里不能有重复值”约束；每张表可以有多个 UNIQUE 约束，其中可以包含允许空值的列。

本章要点

• PRIMARY KEY 与 UNIQUE 的区别
• 单列主键与复合（多列）主键
• 代理键（生成的 id）与自然键（例如电子邮件地址）
• UNIQUE 如何处理 NULL（每个 NULL 都不同，除非使用 NULLS NOT DISTINCT）
• PK / UNIQUE 自动创建的索引

示例

DROP TABLE IF EXISTS users_example;

-- Inline primary key + unique
CREATE TABLE users_example (
  id    integer PRIMARY KEY,
  email text UNIQUE NOT NULL,
  name  text NOT NULL
);

INSERT INTO users_example (id, email, name) VALUES
  (1, 'alice@example.com', 'Alice'),
  (2, 'bob@example.com',   'Bob');

SELECT * FROM users_example ORDER BY id;

-- Composite primary key (across two columns)
DROP TABLE IF EXISTS enrollments_example;
CREATE TABLE enrollments_example (
  student_id integer,
  course_id  integer,
  enrolled_at timestamptz NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (student_id, course_id)
);

INSERT INTO enrollments_example (student_id, course_id) VALUES (1, 100), (1, 200), (2, 100);
SELECT * FROM enrollments_example ORDER BY student_id, course_id;

-- See indexes that the constraints created
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename IN ('users_example', 'enrollments_example')
ORDER BY indexname;

主键将 NOT NULL 与 UNIQUE 结合起来，并创建一个 B-树索引。PostgreSQL 使用主键索引执行连接、处理 WHERE id = ? 查找，并把它作为外键中的自然行标识符。每个设计良好的表都应该有一个主键。

UNIQUE 是更灵活的近亲。每张表可以有多个唯一约束——UNIQUE (email)、UNIQUE (handle)。UNIQUE 约束允许空值；按照标准，NULL 与其他每个 NULL 都不同，因此同一唯一列可以在多行中取 NULL。从 PostgreSQL 15 开始，如果希望把 NULL 视为重复值，可以用 UNIQUE NULLS NOT DISTINCT 覆盖这种行为。

复合键（PRIMARY KEY (a, b)）常用于表示多对多关系的关联表（一个学生可以选修多门课程；一门课程可以有多名学生；这两个值的组合是唯一的）。

运行示例

运行方式：

$ psql -f source/primary-keys-and-unique.sql postgres
DROP TABLE
CREATE TABLE
INSERT 0 2
 id |       email       | name
----+-------------------+-------
  1 | alice@example.com | Alice
  2 | bob@example.com   | Bob
(2 rows)
 ...

常见陷阱

• 允许空值的 UNIQUE 列：默认情况下，两个空值不算重复项。如果你的业务规则是“该列可以省略，但一旦设置就必须唯一”，默认行为正是如此；但如果规则要求“把空值视为相等”，请使用 UNIQUE NULLS NOT DISTINCT（PG 15+）。带有 WHERE col IS NOT NULL 的部分唯一索引仍允许多个空值，不能替代这种约束。
• 选择 email 这样的自然键作为主键看似简洁——直到用户修改了自己的电子邮件地址。代理键（id + UNIQUE (email)）通常更安全。
• 复合主键会传递到外键中；引用复合主键的子表必须同时包含这两列。通常，更简单的设计是为父表设置一个代理 id。
• PRIMARY KEY 和 UNIQUE 都会自动创建一个索引；不要在同一列上再创建另一个 B-树索引。

下载

SQL 文件：primary-keys-and-unique.sql · 全部示例文件 · 准备工作

20 外键

一个外键是一种约束，它规定“此列中的值必须已经作为某个其他表中的主键（或唯一键）存在”。关系型数据库正是通过它来实施引用完整性的——你不能让子行指向不存在的父行，从而成为孤儿行；（取决于你选择的规则）数据库还可以级联或限制对父行的更新。凡是涉及多个表的关系模型，都需要外键。

本章要点

• 使用 REFERENCES 声明外键
• ON DELETE 和 ON UPDATE 动作：NO ACTION、RESTRICT、CASCADE、SET NULL、SET DEFAULT
• 为什么在外键列上建立索引几乎总是个好主意
• DEFERRABLE 约束如何让你在一个事务中批量执行变更
• 检查系统目录中的外键

示例

-- Parent table
DROP TABLE IF EXISTS comments_example;
DROP TABLE IF EXISTS authors_example;

CREATE TABLE authors_example (
  id   integer PRIMARY KEY,
  name text NOT NULL
);

-- Child table referencing parent
CREATE TABLE comments_example (
  id        integer PRIMARY KEY,
  author_id integer NOT NULL REFERENCES authors_example(id) ON DELETE CASCADE,
  body      text NOT NULL,
  parent_id integer REFERENCES comments_example(id) ON DELETE SET NULL  -- self-reference!
);

-- Index the FK column so joins and cascading deletes don't seq-scan
CREATE INDEX ON comments_example (author_id);
CREATE INDEX ON comments_example (parent_id);

INSERT INTO authors_example VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO comments_example (id, author_id, body) VALUES
  (1, 1, 'First!'),
  (2, 2, 'Reply'),
  (3, 1, 'Nice');
UPDATE comments_example SET parent_id = 1 WHERE id = 2;

-- Refused: author 99 does not exist
-- INSERT INTO comments_example (id, author_id, body) VALUES (4, 99, 'orphan');

-- Cascade: deleting Alice removes her comments (and Bob's reply loses its parent_id)
DELETE FROM authors_example WHERE id = 1;
SELECT * FROM comments_example ORDER BY id;

-- Inspect foreign keys
SELECT conname, pg_get_constraintdef(oid) AS def
FROM pg_constraint
WHERE contype = 'f' AND conrelid = 'comments_example'::regclass;

REFERENCES other_table(column) 用于声明外键。被引用的列必须是另一个表的主键或唯一约束。如果省略列名，就假定引用该表的主键：在这里，REFERENCES authors_example 等价于 REFERENCES authors_example(id)。

当父行被删除，或它的键发生变化时，ON DELETE 和 ON UPDATE 动作决定会发生什么：

• NO ACTION（默认）：如果仍有任何子行引用该父行，就报错；在语句结束时检查。
• RESTRICT：类似 NO ACTION，但会立即检查（不能延迟）。
• CASCADE：同时删除或更新子行。如果子行属于父行，这很有用。
• SET NULL：将子表中的列设为 NULL（该列必须允许空值）。
• SET DEFAULT：将子表中的列设为其默认值。

每条 INSERT/UPDATE/DELETE 都会检查外键——它们并非没有代价。外键也是模式中最重要的正确性保障之一；与避免数据损坏相比，这项代价几乎总是值得的。

运行示例

运行方式：

$ psql -f source/foreign-keys.sql postgres
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
INSERT 0 2
INSERT 0 3
UPDATE 1
DELETE 1
 id | author_id | body  | parent_id
----+-----------+-------+-----------
  2 |         2 | Reply |
(1 row)
 ...

常见陷阱

• 忘记在外键列上建立索引：删除父行时，PostgreSQL 必须检查（并可能对其子行执行级联操作）；如果外键列没有索引，这会执行一次顺序扫描。务必给外键列建立索引。
• 过于轻率地选择 CASCADE：生产环境中的一次删除可能级联到数百万行。请审查你使用的 ON DELETE CASCADE 规则，尤其是面向用户的表。
• 导入存在跨表依赖的数据：先装载父表，再装载子表。或者使用 DEFERRABLE INITIALLY DEFERRED，让外键检查在提交时进行，而不是逐行进行。
• 外键之间的循环依赖要求使用可延迟约束。PostgreSQL 支持这种约束，但如果可能，设计时应围绕这一需求安排结构。

下载

SQL 文件：foreign-keys.sql · 全部示例文件 · 准备工作

21 NOT NULL 与 DEFAULT

NOT NULL 和 DEFAULT 是最有用的两种列级规则。NOT NULL 表示该列始终有值——每次 INSERT 和 UPDATE 都必须提供一个值（或依赖默认值）。省略该列时，DEFAULT expr 会提供这个值。两者结合后，模式更容易理解：如果列设为 NOT NULL，应用程序代码就不必到处写 IF column IS NULL。

本章要点

• 使用 NOT NULL 强制要求有值
• 使用 DEFAULT expr 提供默认值
• 易变默认值与常量默认值的区别，以及这对大规模场景意味着什么
• 组合使用 NOT NULL 和 DEFAULT，确保“始终有值”
• 以后删除或修改默认值

示例

DROP TABLE IF EXISTS tasks_example;

CREATE TABLE tasks_example (
  id          integer PRIMARY KEY,
  title       text NOT NULL,
  done        boolean NOT NULL DEFAULT false,
  priority    integer NOT NULL DEFAULT 3 CHECK (priority BETWEEN 1 AND 5),
  due_at      timestamptz,                              -- nullable
  created_at  timestamptz NOT NULL DEFAULT current_timestamp
);

-- Defaults fill in done, priority, created_at
INSERT INTO tasks_example (id, title) VALUES (1, 'Learn SQL');

-- Explicit values override the defaults
INSERT INTO tasks_example (id, title, done, priority, due_at)
VALUES (2, 'Ship lesson', true, 1, current_timestamp + interval '1 day');

SELECT * FROM tasks_example ORDER BY id;

-- Remove a default later (or change it)
ALTER TABLE tasks_example ALTER COLUMN priority SET DEFAULT 1;
ALTER TABLE tasks_example ALTER COLUMN priority DROP DEFAULT;
ALTER TABLE tasks_example ALTER COLUMN priority SET DEFAULT 3;

-- Adding NOT NULL to a column requires no NULLs to exist
-- ALTER TABLE tasks_example ALTER COLUMN due_at SET NOT NULL;  -- would fail if any due_at is NULL

NOT NULL 是代价最低、影响最大的约束之一——它消除了一整类错误（忘记检查空值、聚合结果错误、连接结果出人意料）。只要应用逻辑保证某列一定有值，就应将它标记为 NOT NULL。

DEFAULT 在插入时求值，而不是在创建表时求值。DEFAULT current_timestamp 会针对每个插入的行运行一次 current_timestamp，返回事务开始时间。DEFAULT gen_random_uuid() 会为每一行生成一个新的 UUID。

向已有表添加常量默认值（DEFAULT 0、DEFAULT 'pending'）速度很快——PostgreSQL 11+ 会把这个常量默认值存入元数据，而无需重写表。包括 STABLE 表达式 current_timestamp 在内的非易变默认值也可以使用快速路径。random() 和 clock_timestamp() 等易变默认值需要为每一行已有数据提供一个值，因此会重写表。

运行示例

运行方式：

$ psql -f source/not-null-and-default.sql postgres
DROP TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
 id |    title    | done | priority | due_at | created_at
----+-------------+------+----------+--------+-------------
  1 | Learn SQL   | f    |        3 |        | 2024-xx-xx
  2 | Ship lesson | t    |        1 | 2024-xx-xx | 2024-xx-xx
(2 rows)
 ...

常见陷阱

• 把 ''（空字符串）当成“缺失”——请选择一种约定，并始终保持一致。大多数团队用 NULL 表示“没有值”，把 '' 留给“有意留空”的情况。混用两者，会让查询永远处于半好半坏的状态。
• 默认值不会在 UPDATE 时自动重新应用。要“重置为默认值”，请使用 UPDATE t SET col = DEFAULT WHERE ...。
• 向已有列添加 NOT NULL 时，如果该列中可能已有空值，操作会失败。先回填该列，再执行 SET NOT NULL。
• 当每一列都有默认值时，INSERT INTO t DEFAULT VALUES 很有用——它让数据库填充所有值。

下载

SQL 文件：not-null-and-default.sql · 全部示例文件 · 准备工作

22 标识列与序列

你很少需要手动选择主键值。PostgreSQL 提供两种自动生成主键值的方式：现代的 SQL 标准语法 GENERATED ... AS IDENTITY，以及较旧的 serial / bigserial 简写。两者都由一个序列驱动——序列是一个独立对象，负责发放递增的数字。新代码推荐使用 IDENTITY；旧代码库中仍然常见 serial。

本章要点

• GENERATED ALWAYS AS IDENTITY 语法
• 与较旧的 serial 简写的区别
• 序列是什么，以及如何检查序列
• 为什么生成的 ID 可能出现间断
• 批量装载后重置序列

示例

-- Modern style: GENERATED AS IDENTITY (recommended)
DROP TABLE IF EXISTS messages_example;
CREATE TABLE messages_example (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  body text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT current_timestamp
);

INSERT INTO messages_example (body) VALUES ('first'), ('second'), ('third');
SELECT id, body FROM messages_example ORDER BY id;

-- Legacy serial shortcut (rewrites to a sequence + DEFAULT under the hood)
DROP TABLE IF EXISTS legacy_example;
CREATE TABLE legacy_example (
  id   serial PRIMARY KEY,
  name text NOT NULL
);

INSERT INTO legacy_example (name) VALUES ('a'), ('b');
SELECT id, name FROM legacy_example ORDER BY id;

-- Inspect sequences
SELECT sequencename, last_value, increment_by
FROM pg_sequences
WHERE sequencename IN ('messages_example_id_seq', 'legacy_example_id_seq');

-- Manually grab the next value (and consume it)
SELECT nextval('messages_example_id_seq') AS next_id;
SELECT currval('messages_example_id_seq') AS curr_id;

-- Reset a sequence (e.g. after a bulk import that supplied IDs)
SELECT setval('legacy_example_id_seq',
              (SELECT max(id) FROM legacy_example));