PostgreSQL冲突时插入新行并生成新主键而非更新的方法

你当前的ON CONFLICT DO UPDATE会修改原冲突行的主键，不符合保留原数据、插入新行的需求。正确思路是将无冲突数据直接插入，冲突数据生成新主键后作为新记录插入，以下是具体实现方案：

方案一：分两次插入（清晰直观） #

1. 插入无主键冲突的数据（保留原id） #

INSERT INTO table1 (id, name)
SELECT id, name
FROM table2
WHERE NOT EXISTS (SELECT 1 FROM table1 WHERE table1.id = table2.id);

这一步会把table2中id未在table1中出现的记录（比如id=2的John）直接插入，保留原主键。

2. 处理冲突数据，生成新id插入 #

INSERT INTO table1 (name)
SELECT name
FROM table2
WHERE EXISTS (SELECT 1 FROM table1 WHERE table1.id = table2.id);

如果table1的id字段是自增类型（如serial或GENERATED AS IDENTITY），插入时不指定id会自动调用序列生成新主键（比如冲突的id=3的Jack会生成id=4），避免手动计算max(id)+1带来的并发问题。

方案二：单语句合并实现 #

如果希望用一条SQL完成操作，可以通过CASE判断冲突并生成新id：

INSERT INTO table1 (id, name)
SELECT 
    CASE 
        WHEN EXISTS (SELECT 1 FROM table1 t1 WHERE t1.id = t2.id) 
        THEN nextval(pg_get_serial_sequence('table1', 'id')) 
        ELSE t2.id 
    END AS id,
    t2.name
FROM table2 t2;

这里pg_get_serial_sequence('table1', 'id')会自动获取id字段对应的序列，nextval调用序列生成唯一的新主键，比直接使用(SELECT MAX(id) FROM table1) + 1更安全，能避免并发场景下的主键重复问题。

执行以上任意方案后，table1的结果将符合你的预期：

id | name
----------
1  | Bob
2  | John
3  | Steven
4  | Jack

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