15. 自增列：SERIAL vs GENERATED AS IDENTITY

每张表几乎都要一个主键。我们希望主键值自动递增、不重复、不用手填。PostgreSQL 有两种做法：SERIAL 和 GENERATED AS IDENTITY。v18 推荐后者。

本章是重点，因为「自增主键」几乎每个表都会用到，选错写法会带来隐患。我把两者的本质、差异、以及怎么迁移都讲清楚。先给结论：新项目一律用 GENERATED AS IDENTITY。

推荐写法：GENERATED AS IDENTITY

GENERATED AS IDENTITY 是 SQL 标准写法（在 SQL:2003 里就定义了）。它背后也是用序列（sequence）实现的，但约束更强，和主键的语义绑定得更紧。

CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(120),
  age SMALLINT,
  created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO users (username, email) VALUES ('alice', 'a@x.com');
SELECT * FROM users;

插入时不写 id，数据库自动生成，从 1 开始依次递增。

ALWAYS 与 BY DEFAULT

这是 IDENTITY 的关键选择，决定「手动写主键值」时会发生什么。很多教程漏讲这块，结果一插入自定义 id 就懵。

GENERATED ALWAYS：总是用系统生成的值。手动插主键值会报错：

INSERT INTO users (id, username) VALUES (2, 'bob');
-- ERROR: cannot insert into column "id"
-- HINT: Use OVERRIDING SYSTEM VALUE to override.

真要覆盖，得显式加 OVERRIDING SYSTEM VALUE：

INSERT INTO users (id, username) OVERRIDING SYSTEM VALUE
VALUES (2, 'bob');

GENERATED BY DEFAULT：系统照常生成，但你要手写值它也接受，不会报错：

CREATE TABLE users (
  id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  username VARCHAR(50) NOT NULL
);

INSERT INTO users (id, username) VALUES (2, 'bob');  -- 手动值被采用

序列参数也能配

IDENTITY 底层是序列，可以指定起始值和步长：

CREATE TABLE users (
  id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 100 INCREMENT BY 10) PRIMARY KEY,
  username VARCHAR(50) NOT NULL
);

这样 id 从 100 开始、每次加 10（100、110、120…）。START WITH 设起点，INCREMENT BY 设步长，MINVALUE/MAXVALUE 设上下限，CACHE 设缓存个数。日常用默认值（从 1 起、步长 1）最多。想看当前序列值用：

SELECT pg_get_serial_sequence('users', 'id');  -- 查出底层序列名

SERIAL 仅是历史兼容

SERIAL 是 PostgreSQL 老写法，本质语法糖：建个序列，再把列默认值设成 nextval。还有 smallserial/bigserial 是对应的小/大整数版。

-- 历史写法，新项目不推荐
CREATE TABLE legacy (id SERIAL PRIMARY KEY);

它等价于一串语句：建一个序列、把列默认值设为 nextval(序列)，再把序列归属给这列。看起来方便，问题却不少：

1. 不是 SQL 标准，移植到别的数据库几乎要重写。
2. 约束弱：可以手动插任意值、NOT NULL 也需另加，容易出漏洞。
3. 删列或清表时，背后的序列不会自动跟着清理干净，可能留孤儿序列。
4. 它只是「默认值」机制，数据库并不真正理解这是「身份列」，没法阻止你覆盖值。

所以本书从一开始就统一用 GENERATED ALWAYS AS IDENTITY，SERIAL 只在读老代码时认得即可。

给已有表加自增

表建好后也能把某列改成 IDENTITY，但该列必须先有 NOT NULL：

ALTER TABLE users
ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;

切换 ALWAYS / BY DEFAULT：

ALTER TABLE users
ALTER COLUMN id SET GENERATED BY DEFAULT;

如果列原本是 SERIAL，想升级成 IDENTITY，可以把原序列接上，避免计数从 1 重来：

-- 先看原序列名
SELECT pg_get_serial_sequence('users', 'id');  -- 比如 users_id_seq

ALTER TABLE users ALTER COLUMN id DROP DEFAULT;
ALTER TABLE users ALTER COLUMN id
  ADD GENERATED ALWAYS AS IDENTITY (START WITH 1000);

溢出与上限

自增列也有上限：用 INT（4 字节）最多约 21 亿，表行数可能超这个量时要选 BIGINT（8 字节）。我见过订单表用 INT 自增，跑几年撞到上限，插入全失败，改类型又很折腾。

-- 行数可能很大的表，用 bigint 做自增
CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id INT,
  amount NUMERIC(10,2),
  status VARCHAR(20),
  created_at TIMESTAMPTZ DEFAULT now()
);

对比一览

对比项	SERIAL	GENERATED AS IDENTITY
标准性	PostgreSQL 私有	SQL 标准
约束强度	弱（只是默认值）	强（ALWAYS 防误写）
手动插值	允许	ALWAYS 报错，BY DEFAULT 允许
NOT NULL	需另加	隐含要求
删列清理	序列可能残留	由系统托管
新项目建议	不推荐	推荐

查看序列当前值与剩余空间

IDENTITY 底层是序列，想知道它现在走到哪了，可以用系统函数（需知道底层序列名）：

-- 查某列背后的序列名
SELECT pg_get_serial_sequence('users', 'id');

-- 查看序列当前值（上一次生成的值）
SELECT currval(pg_get_serial_sequence('users','id'));

currval 必须在当前会话里已经生成过值之后才能用，否则会报「尚未定义」。想看「下一个会生成什么」用 nextval，但注意它会真的往前推进一个号。

与应用程序生成主键的对比

除了数据库自增，有些项目喜欢在应用层用雪花算法（snowflake）等生成 id 再插入。两种方式各有取舍：

• 数据库 IDENTITY：简单、有序、省心，但分库分表时要额外处理。
• 应用层生成：适合分布式、可提前拿到 id，但要自己保证不冲突。

无论哪种，PostgreSQL 都支持：IDENTITY 走 GENERATED AS IDENTITY，应用层生成就正常插入你算好的值（用 BY DEFAULT 更顺手）。核心原则不变：主键必须唯一、非空、稳定。

常见误区

有人以为 IDENTITY 和 SERIAL 性能不同，其实底层都靠序列，性能没差别。区别在「语义和约束」，不在速度。选 IDENTITY 是为了数据更安全、代码更标准。还有人担心自增会「用尽」——只要用 BIGINT，几百辈子都用不完，不必焦虑。