PostgreSQL使用ON CONFLICT实现UPSERT时报21000错误如何解决？

该报错本质是pomaster_temp表查询出的待插入数据中，存在重复的POVendorTender值。PostgreSQL的ON CONFLICT DO UPDATE逻辑不允许同一条INSERT命令内，对唯一约束对应的同一行做两次修改，因此直接触发错误。

执行以下SQL查询pomaster_temp中的重复冲突键：

SELECT POVendorTender, COUNT(*) AS repeat_count
FROM (
    SELECT CONCAT(po_number,vendor_code,CAST("TenderID" AS varchar)) AS "POVendorTender"
    FROM pomaster_temp
) t
WHERE POVendorTender IS NOT NULL
GROUP BY POVendorTender
HAVING COUNT(*) > 1;

只要返回结果非空，就说明临时表中存在重复数据，是报错的直接诱因。

方案1：自动去重插入（重复数据保留指定单条即可的场景） #

通过DISTINCT ON对临时表数据按冲突键去重后再插入，可通过ORDER BY指定重复时的保留规则：

INSERT INTO dashboard.tblpurchaseordermaster (po_number,"po_created_TS", 
             vendor_code,"Refreshed_Datetime", 
             "Is_PO_Closed","PO_Closed_Date","TenderID","POVendorTender")
SELECT DISTINCT ON ("POVendorTender") 
    po_number,
    CAST("po_created_TS" AS date),
    vendor_code,
    current_timestamp,
    "Is_PO_Closed",
    "PO_Closed_Date",
    "TenderID",
    CONCAT(po_number,vendor_code,CAST("TenderID" AS varchar)) AS "POVendorTender"
FROM pomaster_temp
-- 示例按po_created_TS倒序，重复时保留最新的一条
ORDER BY "POVendorTender", "po_created_TS" DESC
ON CONFLICT ("POVendorTender") WHERE ("POVendorTender" NOTNULL)
DO UPDATE SET "po_created_TS" = EXCLUDED."po_created_TS",
              "Is_PO_Closed"=EXCLUDED."Is_PO_Closed",
              "PO_Closed_Date"=EXCLUDED."PO_Closed_Date";

方案2：先清理异常重复数据（重复为业务错误的场景） #

根据排查步骤查出的重复POVendorTender值，先删除或修正pomaster_temp中的错误数据，再执行原UPSERT语句即可。

无需额外拼接字段做唯一键，直接使用po_number、vendor_code、TenderID三个字段建立联合唯一索引，性能更好，也能避免字段为NULL时CONCAT结果不符合预期的潜在问题。

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