UPSERT with UPDATE on variable column gives error "command cannot affect row a second time"

Question

Hey I'm trying to insert or update(if the constraints are duplicated) based on a query result, these are the create table statement:

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)
);

This is the upsert query:

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

The result_query rows are:

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

Trying to insert in preprocess_things, where data is a jsonb type and expected result is:

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 }

Erwin Brandstetter · Accepted Answer · 2022-02-17T17:42:51.687

Problem

Your CTE result_query produces two rows with the same values for (arrive_date, arrive_location). The first one is inserted, the second one raises a conflict in the INSERT and tries to UPDATE the same row that has just been inserted - which is not possible, as the error message tells you. If the row already existed, you'd get the same error trying to update the same row twice.
For more explanation, see:

Solution

Fold duplicates in the SELECT, either in the CTE directly, or in the SELECT attached to the INSERT. You didn't disclose how to deal with duplicates exactly, but since you adopted my suggestion to UPDATE with preprocess_things.data || EXCLUDED.data, I suppose you want to overwrite same keys (same thing_type) for the same (arrive_date, arrive_location), but merge all distinct keys (distinct thing_type). I do that with jsonb_object_agg() in the CTE. Concatenation in the UPDATE overwrites same keys.

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<>fiddle here

Everything is prepared in the CTE, so I used the short syntax TABLE result_query in the INSERT. About that:

Is there a shortcut for SELECT * FROM?

About the added WHERE clause to suppress empty updates:

How do I (or can I) SELECT DISTINCT on multiple columns?

UPSERT with UPDATE on variable column gives error "command cannot affect row a second time"

1 Answers1

Problem

Solution

Linked