时间戳

首页 > 解决方案 > Postgresql UPSERT 使用 ON CONFLICT 错误

sql - Postgresql UPSERT 使用 ON CONFLICT 错误

问题描述

我正在尝试在 postgresql 中使用 UPSERT。下面是我的 SQL 字符串。

INSERT INTO dashboard.tblpurchaseordermaster (po_number,"po_created_TS", 
             vendor_code,"Refreshed_Datetime", 
             "Is_PO_Closed","PO_Closed_Date","TenderID","POVendorTender")
SELECT 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
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";

我收到一个错误

21000-ON CONFLICT DO UPDATE command cannot affect row a second timeON CONFLICT DO UPDATE 
command cannot affect row a second time--Ensure that no rows proposed for insertion within the 
same command have duplicate constrained values.-

无法弄清楚我在哪里失踪？

PS:是一个索引键，由和POVendorTender的组合生成。po_numbervendor_codeTenderID

标签： sqlpostgresql

解决方案

关于冲突：

带有 ON CONFLICT DO UPDATE 子句的 INSERT 是“确定性”语句。这意味着该命令将不允许多次影响任何单个现有行；出现这种情况时将引发基数违规错误。建议插入的行不应在受仲裁索引或约束约束的属性方面相互重复。

当在单个语句中插入行时，上述内容适用。

推荐阅读