PostgreSQL中替代SQL Server REPLACE的批量插入冲突处理方案

你需要的是批量插入数据时，若主键/唯一键与现有行冲突，则删除旧行再插入新行的功能，以下是具体实现方案及对你现有代码的修正：

核心实现方案（先删后插） #

如果需要严格实现「删除旧行+插入新行」的逻辑（而非更新旧行），可以通过WITH子句分三步完成：

1. 处理批量数据内部的重复（可选但推荐） #

如果批量插入的数据里存在重复的主键/唯一键，先去重保留需要的记录（例如保留最新的一条）：

WITH deduplicated_data AS (
  -- 对要插入的批量数据去重，按conversationcreateat倒序保留每个cid的最新记录
  SELECT cid, ClosedDate, conversationcreateat
  FROM (
    VALUES 
      (1, 'a', 19),
      (1, 'b', 20),
      (2, 'c', 21)
  ) AS temp(cid, ClosedDate, conversationcreateat)
  ORDER BY conversationcreateat DESC
  LIMIT ALL
),
deleted_conflicts AS (
  -- 删除表中与去重后数据冲突的旧行
  DELETE FROM conversation
  WHERE cid IN (SELECT cid FROM deduplicated_data)
)
-- 插入去重后的新数据
INSERT INTO conversation(cid, ClosedDate, conversationcreateat)
SELECT cid, ClosedDate, conversationcreateat FROM deduplicated_data;

2. 简化版（无内部重复数据时用） #

如果确定批量数据内部没有重复主键，可以直接删除冲突行再插入：

WITH deleted_conflicts AS (
  DELETE FROM conversation
  WHERE cid IN (SELECT cid FROM (VALUES (1), (2)) AS temp(cid))
)
INSERT INTO conversation(cid, ClosedDate, conversationcreateat)
VALUES 
  (1, 'b', 20),
  (2, 'c', 21);

对你现有代码的问题修正 #

你写的SQL存在以下几个问题：

• 语法错误：WITH abc AS (...)的括号未闭合，缺少结尾的)；
• 关键字错误：PostgreSQL中引用冲突的新行需要用EXCLUDED，而非SQL Server的currentRow；
• 逻辑冲突：批量数据内包含重复的cid（1出现两次），即使没有表中旧数据，插入时也会因主键约束报错；
• 逻辑不符：你需求是删除旧行再插入，而ON CONFLICT DO UPDATE是更新旧行，无法满足「删除旧行」的要求。

替代方案：用UPDATE实现类似替换（若无需物理删除） #

如果不需要物理删除旧行，仅需将旧行字段替换为新值，可使用PostgreSQL的Upsert语法（更高效）：

INSERT INTO conversation(cid, ClosedDate, conversationcreateat)
VALUES 
  (1, 'b', 20),
  (2, 'c', 21)
ON CONFLICT (cid) DO UPDATE SET
  ClosedDate = EXCLUDED.ClosedDate,
  conversationcreateat = EXCLUDED.conversationcreateat;

该语句会在主键冲突时直接更新旧行的对应字段，效果与「删旧插新」类似，但不会触发删除相关的触发器。

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