嘗試從 postgresql 中的 CTE 傳遞其他列時，ON CONFLICT DO UPDATE 命令不能再次影響行

根據提供的 SO：https ://dba.stackexchange.com/a/46477答案，我一直**在 Postgres 9.5 或更高版本中使用 UPSERT 擴展函式，**以將數據插入多個引用的表中。

我有以下type要傳遞給函式的內容：

CREATE TYPE symbol_record AS
(
   name                       character varying(32),
   exchange                   character varying(64),

   base_name                  character varying(16),
   base_precision             smallint,
   base_commission_precision  smallint,
   
   quote_name                 character varying(16),
   quote_precision            smallint,
   quote_commission_precision smallint
);

以下相關的 CTE：

-- select all the distinct base_name and quote_name
typ_asset AS (SELECT v.name, a.id
   FROM (
       SELECT DISTINCT (base_name) name FROM val
       UNION
       SELECT DISTINCT (quote_name) name FROM val
   ) v
   LEFT JOIN asset a USING (name)
),

和

ins_asset AS (INSERT INTO asset AS a (name, created_at)
   SELECT name, now()
   FROM typ_asset
   WHERE id IS NULL
   ON CONFLICT (name) DO UPDATE
       SET name = EXCLUDED.name
           WHERE a.name <> EXCLUDED.name
   RETURNING a.name, a.id
),

工作正常。

但是，我不確定如何為每個基本/引用返回匹配的“precision”和“commission_precision”列，以供呼叫 insert/select in 使用ins_asset。

我試過：

typ_asset AS (SELECT v.name, v."precision", v.commission_precision, a.id
   FROM (
       SELECT DISTINCT (base_name) name, (base_precision) "precision", (base_commission_precision) commission_precision FROM val
       UNION
       SELECT DISTINCT (quote_name) name, (quote_precision) "precision", (quote_commission_precision) commission_precision FROM val
   ) v
   LEFT JOIN asset a USING (name)
),

並將列相應地添加到ins_assetCTE，但出現錯誤：

錯誤：ON CONFLICT DO UPDATE 命令不能再次影響行

我目前正在使用 Postgres 14，表格和完整功能可以在以下位置看到： https ://dbfiddle.uk/?rdbms=postgres_14&fiddle=bfcb7f7a9a9fcbd8a1b65ec033af8ac2

我確信它與向asset表中插入額外數據（typ_asset / ins_asset）ctes有關，因為它可以在不傳遞額外列的情況下正常工作（或者我錯誤地將它們傳遞回來）

此外，是的，我完全知道我使用的是保留關鍵字（精度）。由於這是我現在保留的商業語言/術語（必要時引用），但它也將被修復。

返回“precision”和“commission_precision”列並將它們插入asset表中的正確方法是什麼？資產表中的“名稱”列是唯一的，我嘗試設置commission_precision 而不是名稱，以防唯一列是錯誤的原因。這也因同樣的錯誤而失敗。

根據ypercubeᵀᴹ的要求編輯 （1），資產表中沒有精度和佣金精度值的完整工作小提琴： https ://dbfiddle.uk/?rdbms=postgres_14&fiddle=9f7ad113c6c812c1348bbc85eef24661

(2) 對不起，是的，你是對的，我一直在嘗試許多不同的方法來解決問題，並且DISTINCT ON是我嘗試過的一件事（因為我不完全理解語言），顯然，還有其他幾項更改。我已經糾正了不工作的 dbfiddle，這實際上解決了問題。

由於子查詢引起的問題：

FROM (
   SELECT DISTINCT (base_name) name, (base_precision) "precision", (base_commission_precision) commission_precision FROM val
   UNION
   SELECT DISTINCT (quote_name) name, (quote_precision) "precision", (quote_commission_precision) commission_precision FROM val
) v

生成名稱相同但精度或佣金精度不同的行。

您需要修復它，以便每個名稱只生成一行 - 根據您的要求。可能有不同的情況：

• 具有相同 base_name 但不同 base_precision/base_commision_precision 的行。
• 具有相同quote_name 但不同quote_precision/quote_commision_precision 的行。
• base_name 與其他行的 quote_name 匹配的行（以及不同的精度/commision_precision）。

其他要點：

• 列不需要在括號中。
• 以下程式碼部分看起來完全沒有做任何事情：

 ON CONFLICT (name) DO UPDATE
     SET name = EXCLUDED.name
         WHERE a.name <> EXCLUDED.name

引用自：https://dba.stackexchange.com/questions/305753