Postgres插入数据时如何按指定字段校验仅插入不存在的新记录

方案1：使用ON CONFLICT特性（推荐，并发安全） #

这是PostgreSQL特有的UPSERT语法，性能最高，并发场景下也不会出现重复插入问题，使用前需要先为判断重复的三个字段组合创建唯一约束：

-- 为table2创建三个字段组合的唯一约束
ALTER TABLE table2 ADD CONSTRAINT uk_table2_name_source_sink UNIQUE ("Name", "Source_NE", "Sink_NE");

修改你的Python代码中INSERT语句，增加冲突跳过逻辑即可，完整代码如下：

cursor_local.execute("""SELECT "Name", "Level_Capacity", "Source_NE", "Sink_NE" FROM "table1" WHERE "Name" LIKE '%WDM%' """)
rows = cursor_local.fetchall()

psycopg2.extras.execute_batch(cursor_local, 
'''INSERT INTO table2("Name", "Level_Capacity", "Source_NE", "Sink_NE") 
VALUES (%s, %s, %s, %s) 
-- 三个字段组合冲突时直接跳过插入
ON CONFLICT ("Name", "Source_NE", "Sink_NE") DO NOTHING''', 
(*rows,) )
connection_local.commit()

方案2：使用INSERT ... WHERE NOT EXISTS（无需修改表结构） #

如果不方便修改table2的表结构添加唯一约束，可以用此方案，仅适合单线程无并发插入的场景：

cursor_local.execute("""SELECT "Name", "Level_Capacity", "Source_NE", "Sink_NE" FROM "table1" WHERE "Name" LIKE '%WDM%' """)
rows = cursor_local.fetchall()

psycopg2.extras.execute_batch(cursor_local, 
'''INSERT INTO table2("Name", "Level_Capacity", "Source_NE", "Sink_NE") 
SELECT %s, %s, %s, %s
WHERE NOT EXISTS (
    SELECT 1 FROM table2 
    WHERE "Name" = %s 
      AND "Source_NE" = %s 
      AND "Sink_NE" = %s
)''', 
# 参数按顺序对应，三个判断字段需要传两次
[(row[0], row[1], row[2], row[3], row[0], row[2], row[3]) for row in rows] )
connection_local.commit()

注意事项 #

如果Name/Source_NE/Sink_NE三个字段存在NULL值，PostgreSQL默认唯一约束会将两个NULL判定为不相等，若需要将NULL视为相同值做去重，可以将唯一约束替换为带COALESCE的唯一索引：

-- 替换NULL_PLACEHOLDER为业务中不会出现的占位字符串即可
CREATE UNIQUE INDEX idx_table2_unique_combine ON table2 (
    "Name", 
    COALESCE("Source_NE", 'NULL_PLACEHOLDER'), 
    COALESCE("Sink_NE", 'NULL_PLACEHOLDER')
);

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