主页 / dba / 问题 / 307645
Accepted
IBot
Asked: 2022-02-18 06:21:49 +0800 CST

对变量列进行更新的 UPSERT 给出错误“命令不能再次影响行”

  • 772

嘿，我正在尝试根据查询结果插入或更新（如果约束重复），这些是 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 }
postgresql duplication

1 个回答

  1. Best Answer

    问题

    您的 CTEresult_query生成两行具有相同值的(arrive_date, arrive_location). 插入第一个，第二个引发冲突，INSERT并尝试UPDATE插入刚刚插入的同一行 - 这是不可能的，正如错误消息告诉你的那样。如果该行已经存在，则尝试两次更新同一行时会出现相同的错误。
    更多解释见：

    解决方案

    折叠 中的重复项，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. 关于那个：

    关于WHERE禁止空更新的添加子句：

    • 1

相关问题