未传入ID却触发PostgreSQL表重复ID错误，如何插入新行？

问题描述 #

通过Mockaroo生成了含约1000行数据的person表，表结构如下：

Column      |         Type          | Collation | Nullable |              Default
------------------+-----------------------+-----------+----------+------------------------------------
 id               | bigint                |           | not null | nextval('person_id_seq'::regclass)
 first_name       | character varying(100) |           | not null |
 last_name        | character varying(100) |           | not null |
 gender           | character varying(7) |           | not null |
 email            | character varying(100) |           |          |
 date_of_birth    | date                  |           | not null |
 country_of_birth | character varying(100) |           | not null |
Indexes:
    "person_pkey" PRIMARY KEY, btree (id)
    "person_email_key" UNIQUE CONSTRAINT, btree (email)

id字段为BIGSERIAL类型（依赖person_id_seq序列自动生成递增ID），但执行插入语句时触发重复ID错误：

test=# INSERT INTO person (first_name, last_name, gender, email, date_of_birth, country_of_birth) VALUES ('Sean', 'Paul','Male', 'paul@gmail.com','2001-03-02','India');
ERROR:  duplicate key value violates unique constraint "person_pkey"
DETAIL:  Key (id)=(2) already exists.

原因分析 #

Mockaroo导入数据时直接指定了id字段的值，但未同步更新person_id_seq序列的当前值，导致序列的下一个生成值小于或等于表中已存在的最大id，插入时自动生成的ID与现有数据冲突。

解决方案 #

步骤1：查询表中最大的ID值 #

先确认当前表中已存在的最大id，确定序列需要重置的起始值：

SELECT MAX(id) FROM person;

假设查询结果为1000，则序列需要从1001开始生成新ID。

步骤2：重置序列的当前值 #

使用以下SQL语句将序列的下一个生成值设置为最大ID+1：

-- 方法1：ALTER SEQUENCE 语法
ALTER SEQUENCE person_id_seq RESTART WITH (SELECT MAX(id) + 1 FROM person);

-- 方法2：setval 函数（PostgreSQL全版本兼容）
SELECT setval('person_id_seq', (SELECT MAX(id) FROM person) + 1);

步骤3：重新执行插入语句 #

此时再执行原插入语句，就能成功插入新行：

INSERT INTO person (first_name, last_name, gender, email, date_of_birth, country_of_birth) 
VALUES ('Sean', 'Paul','Male', 'paul@gmail.com','2001-03-02','India');

内容的提问来源于stack exchange，提问作者Sejal Dahake