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



  • 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 }
    


  • 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:

    • https://dba.stackexchange.com/questions/206185/multiple-on-conflict-targets/225886#225886

    • https://stackoverflow.com/a/35953488/939860

    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 https://dba.stackexchange.com/a/307608/3684 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 https://www.postgresql.org/docs/current/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE 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 https://dbfiddle.uk/?rdbms=postgres_14&fiddle=2fc81795067b7d7ae01fa1d688bbd229

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

    • https://stackoverflow.com/a/30276023/939860

    About the added WHERE clause to suppress empty updates:

    • https://stackoverflow.com/a/12632129/939860



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2