對變數列進行更新的 UPSERT 給出錯誤“命令不能再次影響行”

嘿，我正在嘗試根據查詢結果插入或更新（如果約束重複），這些是 create table 語句：

CREATE TABLE IF NOT EXISTS public.inventory (
 inventory_id serial PRIMARY KEY,
 arrive_date date NOT NULL,
 arrive_location character varying NOT NULL,
 thing_type integer NOT NULL,
 quantity integer NOT NULL
);

CREATE TABLE IF NOT EXISTS public.preprocess_things (
 preprocess_id serial PRIMARY KEY,
 arrive_date date NOT NULL,
 arrive_location character varying NOT NULL,
 data jsonb NOT NULL,
 CONSTRAINT preprocess_things_arrive_date_arrive_location_bo_key UNIQUE (arrive_date, arrive_location)
);

這是 upsert 查詢：

WITH result_query AS (
   SELECT DATE_TRUNC('day', arrive_date) AS date,
     arrive_date,
     arrive_location,
     thing_type,
     SUM(quantity) AS total_things
   FROM inventory
   GROUP BY date, arrive_location, thing_type
)
INSERT INTO preprocess_things (
   arrive_date,
   arrive_location,
   data
 )
SELECT r.date AS arrive_date,
 r.arrive_location,
 jsonb_build_object(r.thing_type, r.total_things)
FROM result_query r
ON CONFLICT (arrive_date, arrive_location) DO
UPDATE SET data = preprocess_things.data || EXCLUDED.data

result_query 行是：

date                   | arrive_location | thing_type | thing_count
2018-05-30 00:00:00-00 | location_00     |   3        | 2
2018-05-31 00:00:00-00 | location_00     |   3        | 8
2018-05-31 00:00:00-00 | location_00     |   4        | 7

嘗試插入preprocess_things，其中 data 是一種jsonb類型，預期結果是：

id | arrive_date            | arrive_location | data
1  | 2018-05-30 00:00:00-00 | location_00     | { "3": 2 }
2  | 2018-05-31 00:00:00-00 | location_00     | { "3": 8, "4": 7 }

問題

您的 CTEresult_query生成兩行具有相同值的(arrive_date, arrive_location). 插入第一個，第二個引發衝突，INSERT並嘗試UPDATE插入剛剛插入的同一行 - 這是不可能的，正如錯誤消息告訴你的那樣。如果該行已經存在，則嘗試兩次更新同一行時會出現相同的錯誤。

更多解釋見：

• 多個衝突目標
• 如何在 RETURNING from INSERT 中包含排除的行… ON CONFLICT

解決方案

折疊 中的重複項，SELECT可以直接在 CTE中，也可以在SELECT附加到INSERT. 您沒有透露如何準確處理重複項，但由於您採納了我對UPDATEwith的建議preprocess_things.data || EXCLUDED.data，我想您想為相同的鍵覆蓋相同的鍵（相同thing_type）(arrive_date, arrive_location)，但合併所有不同的鍵（不同的鍵thing_type）。我jsonb_object_agg()在 CTE 中這樣做。UPDATE覆蓋相同的鍵的串聯。

WITH result_query AS (
  SELECT arrive_date, arrive_location
       , jsonb_object_agg(thing_type, total_things)  -- !
  FROM  (
     SELECT date_trunc('day', arrive_date) AS arrive_date
            -- additional column "arrive_date" had to go
          , arrive_location
          , thing_type
          , sum(quantity) AS total_things
     FROM   inventory
     GROUP  BY date_trunc('day', arrive_date), arrive_location, thing_type
     ) sub
  GROUP  BY arrive_date, arrive_location
  )
INSERT INTO preprocess_things AS p (arrive_date, arrive_location, data)
TABLE  result_query
ON CONFLICT (arrive_date, arrive_location) DO
UPDATE
SET    data = p.data || EXCLUDED.data
WHERE  p.data IS DISTINCT FROM p.data || EXCLUDED.data;  -- exclude empty updates

db<>在這裡擺弄

一切都在 CTE 中準備好了，所以我TABLE result_query在INSERT. 關於那個：

• SELECT * FROM 有快捷方式嗎？

關於WHERE禁止空更新的添加子句：

• 我如何（或我可以）在多列上選擇 DISTINCT？

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