使用Serial/Identity约束时，如何避免插入数据的主键冲突？

问题核心在于：Serial/Identity类型的主键依赖底层序列生成自动值，但手动插入指定ID后，序列的当前计数器不会自动同步表中的最大ID，导致后续自动生成的ID重复，触发主键冲突。

要基于Serial/Identity解决这个问题，核心是让绑定的序列始终与表中最大ID保持同步，具体方案如下：

1. 找到Serial/Identity对应的底层序列 #

首先确认主键列绑定的序列名称，执行以下SQL：

SELECT pg_get_serial_sequence('tab1', 'id');

（PostgreSQL中，Serial类型的序列默认命名为tab1_id_seq，Identity类型绑定的序列也可通过此语句查询）

2. 自动同步序列与表中最大ID #

创建触发器函数和触发器，在每次插入操作前自动更新序列的当前值，确保它始终不小于表中的最大ID：

创建触发器函数

CREATE OR REPLACE FUNCTION sync_tab1_id_seq()
RETURNS TRIGGER AS $$
BEGIN
    -- 将序列值设置为表中当前最大ID，若表为空则设为1
    PERFORM setval(pg_get_serial_sequence('tab1', 'id'), COALESCE((SELECT MAX(id) FROM tab1), 1));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

创建前置触发器

CREATE TRIGGER trigger_sync_tab1_id
BEFORE INSERT ON tab1
FOR EACH STATEMENT
EXECUTE FUNCTION sync_tab1_id_seq();

3. 一次性修复（可选） #

如果只是临时解决当前冲突，无需长期自动维护，可直接执行以下语句手动同步序列值：

SELECT setval(pg_get_serial_sequence('tab1', 'id'), (SELECT MAX(id) FROM tab1));

这样处理后，第三次插入INSERT INTO tab1(name) VALUES('test3')时，序列会自动取当前表中最大ID（2）加1，生成3作为新ID，避免主键冲突。

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