PostgreSQL使用ON CONFLICT IGNORE实现不存在即插入功能失效如何解决

问题原因 #

你遇到的问题由两个常见错误导致：

• 语法适配错误：ON CONFLICT IGNORE是SQLite的专属语法，PostgreSQL并不支持该写法
• 缺少冲突指定：PostgreSQL的ON CONFLICT子句必须明确指定触发冲突的约束/字段，不能省略

解决步骤 #

1. 先确认冲突判断字段存在唯一约束：你要基于patient_id判断重复的话，需要确保patient_id是表的主键，或者已经添加了UNIQUE唯一约束，如果还没加可以先执行SQL添加：

ALTER TABLE patients_patient ADD UNIQUE (patient_id);

2. 修改插入语句为PostgreSQL支持的正确语法，ON CONFLICT后指定冲突字段，后面跟DO NOTHING实现冲突时忽略插入的效果：

cur.execute(f"""INSERT INTO patients_patient (patient_id, patient_name) VALUES
            (1,'Jane'),(2,'John') ON CONFLICT (patient_id) DO NOTHING""")

补充说明 #

• 如果你需要在冲突时更新已有数据（比如更新患者名称），可以把DO NOTHING换成DO UPDATE SET patient_name = EXCLUDED.patient_name即可实现upsert效果
• 如果你的唯一约束是自定义命名的多字段联合约束，也可以在ON CONFLICT后直接写约束名，比如ON CONFLICT ON CONSTRAINT 约束名 DO NOTHING

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