PostgreSQL 中的 SERIAL 和 IDENTITY 自增

sql CREATE TABLE products ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL );

上面的语句等价于执行了以下几步：

1. 创建一个名为 `products_id_seq` 的序列（命名规则：`表名_列名_seq`）。
2. 将 `id` 列的默认值设置为 `nextval('products_id_seq')`。
3. 将该列标记为 `NOT NULL`（因为 `SERIAL` 保证不会为 NULL）。

执行插入操作时，可以省略 `id` 或填入 `DEFAULT`：

```sql
INSERT INTO products (name) VALUES ('键盘');
INSERT INTO products (id, name) VALUES (DEFAULT, '鼠标');

查询表数据会看到自动生成的编号。

获取刚刚生成的自增值

PostgreSQL 提供了 RETURNING 子句，可以直接在插入后返回自增的 ID：

INSERT INTO products (name) VALUES ('显示器') RETURNING id;

序列的独立操作

由于 SERIAL 背后是一个独立的序列对象，你可以直接操控它：

-- 查看当前序列值
SELECT currval('products_id_seq');
-- 查看下一个值
SELECT nextval('products_id_seq');
-- 手动设置序列的当前值（通常用于数据修复）
SELECT setval('products_id_seq', 100);

二、使用 IDENTITY 列（推荐）

从 PostgreSQL 10 开始，引入了 SQL 标准中的 IDENTITY 列。它的效果与 SERIAL 类似，但更加规范且与序列的关联更紧密。

创建示例

CREATE TABLE orders (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_date DATE NOT NULL
);

这里 GENERATED ALWAYS AS IDENTITY 表示该列的值始终由系统生成。如果你尝试在 INSERT 时指定 id 的值，默认会报错：

-- 这会被拒绝
INSERT INTO orders (id, order_date) VALUES (1, '2025-01-01');

如果需要允许手动插入值（例如数据迁移），可以使用 BY DEFAULT 选项：

CREATE TABLE orders (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    order_date DATE NOT NULL
);

此时，如果没有指定 id，系统会自动生成；如果手动提供了值，则使用提供的值（注意：这可能导致未来自动生成的值与手动插入的值发生冲突）。

IDENTITY 的附加控制

IDENTITY 列可以直接在创建时配置序列参数，比 SERIAL 更灵活：

CREATE TABLE invoices (
    id INT GENERATED ALWAYS AS IDENTITY (
        START WITH 1000 INCREMENT BY 5
    ) PRIMARY KEY,
    amount NUMERIC
);

此外，你还可以通过标准命令修改列的标识属性：

-- 将一个普通的整数列变为标识列（需要表为空或保留数据）
ALTER TABLE some_table ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;

-- 重设标识列的序列值
ALTER TABLE orders ALTER COLUMN id RESTART WITH 500;

三、SERIAL 和 IDENTITY 的核心区别

推荐场景：对于新项目，应优先使用 IDENTITY 列。只有在维护旧系统或需要兼容更低版本的 PostgreSQL 时才使用 SERIAL。

四、常见问题与避坑指南

1. 事务回滚会导致序列“跳号”

自增序列的值一旦通过 nextval() 获取，就不会在事务失败时回退。这是为了高并发性能而设计的，属于正常现象。

BEGIN;
INSERT INTO products (name) VALUES ('测试') RETURNING id; -- 假设返回 5
ROLLBACK;
-- 下一次插入不会再用 5，直接用 6
INSERT INTO products (name) VALUES ('再次测试') RETURNING id; -- 返回 6

如果业务要求连续的、无间隔的序列号，请不要依赖数据库自增列，而应在应用层用计数器实现（但会牺牲并发能力）。

2. 手动插入值与序列冲突

使用 SERIAL 或 BY DEFAULT IDENTITY 时，如果你手动插入了一个 ID，序列并不知道，后续自动生成可能产生重复值冲突。

解决方式：插入后手动同步序列：

SELECT setval('products_id_seq', (SELECT max(id) FROM products));

3. 复制表结构时序列的处理

使用 CREATE TABLE ... AS SELECT * FROM original 不会复制序列的默认值。推荐使用 LIKE 加 INCLUDING IDENTITY：

CREATE TABLE products_backup (LIKE products INCLUDING ALL);

如果原表使用 SERIAL，则用 INCLUDING DEFAULTS 来复制序列默认值。

4. 迁移到 IDENTITY

如果你要把一个基于 SERIAL 的列迁移到 IDENTITY 列，可以这么做：

-- 1. 移除旧的默认值（解除与序列的关联）
ALTER TABLE products ALTER COLUMN id DROP DEFAULT;
-- 2. 将列改为 IDENTITY
ALTER TABLE products ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;
-- 3. 调整序列当前值，确保不冲突
SELECT setval('products_id_seq', max(id)) FROM products;

注意：ADD GENERATED 需要表上有主键或唯一约束（通常是主键），这一操作会锁表，在生产环境请安排维护窗口。

五、实际应用中的最佳实践

• 主键统一使用 IDENTITY：除非兼容老版本，否则新表一律采用 GENERATED ALWAYS AS IDENTITY，保证数据完整性。
• 类型选择要合理：对于大多数表，integer（约 21 亿）足矣；日志或流水表使用 bigint。
• 对外暴露时不直接使用自增 ID：如果 API 对外暴露记录标识，可结合使用不可预测的 UUID 作为公开标识，自增 ID 仅作为内部主键。
• 监控序列溢出风险：定期检查 pg_sequences 中序列的消耗百分比，避免序列耗尽导致插入失败。
• 利用 RETURNING 减少查询：插入后直接用 RETURNING id 获取生成的值，避免额外的 SELECT。

六、快速参考命令

-- 查看所有序列
SELECT * FROM pg_sequences;

-- 查看某列的标识信息
SELECT column_name, is_identity, identity_generation
FROM information_schema.columns
WHERE table_name = 'orders' AND column_name = 'id';

-- 修改序列步长
ALTER SEQUENCE products_id_seq INCREMENT BY 10;
-- 或对 IDENTITY 列直接操作
ALTER TABLE orders ALTER COLUMN id SET INCREMENT BY 10;

-- 重置序列从一个值开始
ALTER SEQUENCE products_id_seq RESTART WITH 100;
-- 或
ALTER TABLE orders ALTER COLUMN id RESTART WITH 100;