PostgreSQL实现同列名两表按主键插入更新执行报错求解

问题根因 #

• PostgreSQL不支持MySQL专属的ON DUPLICATE KEY UPDATE语法，PG实现主键冲突更新（Upsert）的标准语法为ON CONFLICT (冲突约束字段) DO UPDATE
• 错误的col1=SELECT(col1)写法不符合PG语法规范，同时异常的括号结构会被Python PG驱动（如psycopg2）误识别为参数占位符，要求传入第二个参数vars_list，因此抛出你遇到的TypeError
• PG中引用冲突行的待插入数据，需要用固定别名EXCLUDED

解决方案 #

将PG侧代码替换为以下写法即可，注意替换ON CONFLICT后的主键字段为你表实际的主键名：

# 假设table1的主键为id，若为联合主键则写入所有主键字段，逗号分隔
db_cursor.execute("INSERT INTO table1 SELECT * FROM table2 ON CONFLICT (id) DO UPDATE SET col1 = EXCLUDED.col1, col2 = EXCLUDED.col2")

注意事项 #

• 建议将SELECT *替换为显式的列名列表，避免两张表列顺序不一致引发的隐式错误，示例：SELECT id, col1, col2 FROM table2
• 若冲突字段不是主键，是唯一索引的话，也可以在ON CONFLICT后传入唯一索引的字段名，触发更新逻辑
• 修正SQL后无需额外传入第二个参数，驱动不会再误判占位符，TypeError会自动消除

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