如何在 PostgreSQL 中正确设置序列起始值以避免主键冲突

本文详解 PostgreSQL 序列（SEQUENCE）的初始化与同步方法，重点解决手动插入数据后 nextval() 返回错误起始值的问题，涵盖 setval() 调用、序列所有权绑定、以及现代 GENERATED BY DEFAULT AS IDENTITY 的最佳实践。

本文详解 PostgreSQL 序列（SEQUENCE）的初始化与同步方法，重点解决手动插入数据后 `nextval()` 返回错误起始值的问题，涵盖 `setval()` 调用、序列所有权绑定、以及现代 `GENERATED BY DEFAULT AS IDENTITY` 的最佳实践。

在 Spring Boot 迁移脚本中，若先创建自定义序列（如 START WITH 1 INCREMENT BY 5），再手动插入 ID 为 1、2、3 的用户记录，随后调用 ALTER SEQUENCE ... RESTART 并执行 UPDATE ... SET id = DEFAULT，往往无法使新记录从预期值（如 4）开始——这是因为 RESTART 默认重置为 START WITH 值（即 1），而非基于现有数据动态对齐。根本原因在于：序列本身并不感知表中已存在的数据，必须显式同步其当前值。

✅ 正确同步序列：使用 setval()

最直接可靠的方式是调用 setval() 函数，将序列当前值设为表中最大 ID：

SELECT setval('public.sequence_user', (SELECT COALESCE(MAX(id), 0) FROM public."user"));

⚠️ 注意：COALESCE(MAX(id), 0) 确保空表时安全设为 0（下次 nextval() 返回 1）；若希望下个值为 4，则应设为 3（因序列在返回前先递增）。例如：

SELECT setval('public.sequence_user', 3); -- 下次 nextval() 返回 4

? 绑定序列与列：提升可维护性

为长期解耦并避免硬编码序列名，建议将序列“拥有”（OWNED BY）目标列，并设置列为默认值来源：

-- 1. 关联序列与列（自动管理归属）
ALTER SEQUENCE public.sequence_user OWNED BY public."user".id;

-- 2. 设置列默认值为序列
ALTER TABLE public."user" 
  ALTER COLUMN id SET DEFAULT nextval('public.sequence_user');

-- 3. 同步序列至当前最大ID（关键！）
SELECT setval(pg_get_serial_sequence('public."user"', 'id'), 
              (SELECT COALESCE(MAX(id), 0) FROM public."user"));

pg_get_serial_sequence() 可动态获取列关联的序列名，增强脚本健壮性。

? 推荐方案：改用 GENERATED BY DEFAULT AS IDENTITY

PostgreSQL 10+ 提供更标准、更安全的标识列语法，自动创建并管理序列，且语义清晰：

CREATE TABLE public."user" (
  id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  first_name  VARCHAR(50) NOT NULL,
  last_name   VARCHAR(50) NOT NULL,
  username    VARCHAR(20) NOT NULL,
  "password"  VARCHAR(100) NOT NULL
);

此时无需手动创建序列，系统自动生成类似 user_id_seq 的序列，并可通过 pg_get_serial_sequence() 获取。初始化数据后同步方式一致：

-- 手动插入初始数据
INSERT INTO public."user" (id, first_name, last_name, username, "password") VALUES
  (1, 'John', 'Doe', 'johndoe', '...'),
  (2, 'Frank', 'James', 'frank', '...'),
  (3, 'Foo', 'Bar', 'foo', '...');

-- 同步序列至最大ID（确保下次 nextval() 返回 4）
SELECT setval(pg_get_serial_sequence('public."user"', 'id'), 
              (SELECT COALESCE(MAX(id), 0) FROM public."user"));

此后，插入时可省略 id（自动分配）或显式指定 DEFAULT：

INSERT INTO public."user" (first_name, last_name, username, "password")
VALUES ('Adam', 'Savage', 'adamsavage', '...'); -- 自动分配下一个ID（4）

INSERT INTO public."user" (id, first_name, last_name, username, "password")
VALUES (DEFAULT, 'Eve', 'Smith', 'eve', '...'); -- 显式触发序列

? 总结与注意事项

• ❌ 避免仅依赖 ALTER SEQUENCE ... RESTART —— 它不读取表数据，无法自动对齐；
• ✅ 每次手动插入 ID 后，必须执行 setval() 同步序列；
• ✅ 使用 OWNED BY 或 IDENTITY 可显著降低维护成本与出错风险；
• ✅ 在 Spring Boot Liquibase/Flyway 迁移中，将 setval(...) 放在数据插入之后、应用启动之前；
• ? 若序列 INCREMENT BY 5 是业务必需，请确保 setval() 设置的值是 5 的倍数减 1（如期望下个值为 4，则 setval(..., 3)），否则可能跳过或重复。

遵循以上方法，即可确保 PostgreSQL 序列始终从正确位置生成 ID，彻底规避主键冲突与序列错位问题。