22 标识列与序列

使用标识列生成标识符，并理解共享序列的行为。

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

本章要点

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

示例

示例 22.1 标识列与序列

-- 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));

GENERATED ALWAYS AS IDENTITY 告诉 PostgreSQL，该列的值来自底层序列，INSERT 不能覆盖这个值（除非使用 OVERRIDING SYSTEM VALUE）。GENERATED BY DEFAULT AS IDENTITY 是限制更少的变体——如果 INSERT 提供了值，就使用该值；否则由序列提供。若要防止应用程序代码意外注入 ID，请使用 ALWAYS；如果需要为迁移和测试保留灵活性，则使用 BY DEFAULT。

较旧的 serial（integer + 序列）和 bigserial（bigint + 序列）是 integer NOT NULL DEFAULT nextval('seq') 的语法糖。它们仍然可用，但 IDENTITY 是 SQL 标准，与 pg_dump 配合得更好，而且对序列所有权的跟踪更清晰。

序列独立于事务：nextval() 不会回滚。这意味着，如果某个事务插入一行后又回滚，该 ID 就被消耗掉了——下一行会得到它之后的值。ID 可能出现间断，应用程序绝不能假设它们是连续的。

运行示例

运行方式：

$ psql -f source/identity-and-sequences.sql postgres
DROP TABLE
CREATE TABLE
INSERT 0 3
 id | body
----+--------
  1 | first
  2 | second
  3 | third
(3 rows)
 ...

常见陷阱

使用另一个连接中的 currval() 读取“我刚插入的最后一个 ID”——这样行不通。序列是共享对象；只有 currval() 记住的值按会话分别保存。请改用 RETURNING id。
批量装载并提供显式 ID 后，序列仍停留在旧值，因此下一个自动生成的 ID 会发生冲突。请重置它：SELECT setval('t_id_seq', (SELECT max(id) FROM t));。
不要指望 ID 连续或反映插入顺序，尤其是在并发场景下。如果需要排序，请使用 created_at, id。
在数据量很大的表上为标识列使用 integer（32 位）——你可能耗尽取值范围。请使用 bigint。

提示

从 MySQL 的 AUTO_INCREMENT 迁移时，最安全的直接等价写法是 bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY。它接受显式 ID（适合迁移），否则就自动生成。

动手练习

在 messages_example 中执行两次 INSERT，然后在一个事务中对其中一次执行 ROLLBACK（BEGIN; INSERT ...; ROLLBACK;），再执行一次 INSERT——你应该能看到 ID 出现间断。然后使用 setval() 重置序列，观察下一次 INSERT 从那里继续。

22 标识列与序列

本章要点

示例

运行示例

常见陷阱

下载