用 PostgreSQL ON CONFLICT 批量 upsert 上万行

用 COPY 或多行 INSERT 配合 ON CONFLICT DO UPDATE 批量入仓，避开逐行往返与乱序写入死锁。

目标

逐行 UPDATE 循环要为每条语句付出连接开销、往返延迟与独立执行计划。先把行装载进临时表，再用一条 ON CONFLICT 语句完成整批 upsert，可以把四万行四十分钟的夜间同步压缩到几秒，同时通过排序写入消除死锁、通过跳过无变化行消除死元组，让整条管线可重跑、可断点续传。

步骤要点

先查 pg_constraint 确认冲突目标是真实存在的唯一约束（如 products_sku_key UNIQUE (sku)），upsert 的冲突列必须与其完全一致。
用 LIKE 建临时表并通过 COPY 把 CSV 一次装入，避免逐行插入的目录开销。
用单条 INSERT … SELECT … ON CONFLICT (sku) DO UPDATE 完成整批写入，并在 SET 上加 WHERE 过滤未变化的行。
并发同步场景把来源按冲突键排序（子查询 ORDER BY sku），两个作业按相同顺序拿锁，经典 AB-BA 死锁随之消失。
连跑两次同步并统计 updated_at 新鲜度，第二次应当几乎为零变化，证明幂等可重跑、失败可续传。

排错提示

ON CONFLICT 找不到约束时，冲突目标列清单必须与约束定义在列名和顺序上完全一致，先用 pg_get_constraintdef 核对；并发仍有死锁说明某条写入路径没排序，让所有路径按冲突键排序并保留死锁重试兜底；COPY 因编码或引号失败时，导出侧用服务器期望的编码并显式 CSV 引号，或退回每 500 行一条的多行参数化 INSERT。

适用边界

ON CONFLICT DO NOTHING 会静默丢弃冲突行，做去重正确、做同步错误，需按任务选择；超大暂存会撑爆临时文件，单个批次超过 1GB 就分块 COPY。

代码示例

-- Per-row loop: 80,000 statements, 40 minutes, random deadlocks
-- Set-based: 3 statements, seconds, ordered locks
\copy stage_products FROM 'sync.csv' CSV HEADER

INSERT INTO products (sku, name, price, updated_at)
SELECT sku, name, price, now()
FROM (SELECT * FROM stage_products ORDER BY sku) s
ON CONFLICT (sku) DO UPDATE SET
  name = EXCLUDED.name,
  price = EXCLUDED.price,
  updated_at = EXCLUDED.updated_at
WHERE products.price IS DISTINCT FROM EXCLUDED.price;