Upsert on a table that has multiple fields and jsonb column using WITH statement

Question

So I was trying to do an upsert based on a query result:

/*
    Querying from this table:
     id | arrive_date | arrive_location | thing_type | thing_count
*/
CREATE TABLE IF NOT EXISTS public.inventory
(
  inventory_id serial NOT NULL,
  inventory_date date NOT NULL,
  arrive_location character varying NOT NULL,
  thing_type integer NOT NULL,
  quantity integer NOT NULL,
  PRIMARY KEY (inventory_id)
)    
/*
    Trying to insert on this table, where summary is a jsonb type:
    id | arrive_date | arrive_location | data
*/
CREATE TABLE IF NOT EXISTS public.preprocess_things
   (
      preprocess_id serial NOT NULL,
      arrive_date date NOT NULL,
      arrive_location character varying NOT NULL,
      data jsonb NOT NULL,
      CONSTRAINT preprocess_things_pkey PRIMARY KEY (preprocess_id),
      CONSTRAINT preprocess_things_arrive_date_arrive_location_bo_key   UNIQUE (arrive_date, arrive_location)
    )
/*Begin upsert*/
WITH result_query AS (
    SELECT DATE_TRUNC('day', inventory_date) AS arrive_date,
      arrive_location,
      thing_type,
      SUM(quantity) AS total_things
    FROM inventory
    GROUP BY arrive_date, arrive_location, thing_type
)
INSERT INTO preprocess_things (
    result_query.arrive_date,
    result_query.arrive_location,
    jsonb_build_object(result_query.thing_type || '', result_query.total_things)::jsonb
  ) ON CONFLICT (arrive_date, arrive_location) DO
UPDATE
  SET data= jsonb_insert(data, '{' || result_query.thing_type || '}', result_query.thing_sum)

There's an issue:

ERROR:  syntax error at or near "("
LINE 7:     jsonb_build_object(result_query.thing_type || '', total_things)::...

Upsert with mocked data is working, but it does not work sending a parameter to jsonb_build_object

score 0 · Accepted Answer · answered Feb 17 '22 at 02:24

You probably want something like this:

WITH result_query AS (
   SELECT id, arrive_date, arrive_location, thing_type, thing_count
   FROM   ... -- ?
   )
INSERT INTO preprocess_things (arrive_date, arrive_location, data)  -- ①
SELECT r.arrive_date  -- ②
     , r.arrive_location
     , jsonb_build_object(r.thing_type, r.thing_count)  -- ③
FROM   result_query r
ON     CONFLICT (arrive_date, arrive_location) DO
UPDATE
SET    data = data || EXCLUDED.data;  -- ④

① Always provide a target column list for persisted code.

② The main issue: you have to SELECT from the CTE to bring in the computed values. Your original syntax also switched target and source. Have another look at the INSERT syntax.

③ Assuming you meant jsonb_build_object(r.thing_type, r.thing_count). What you had there didn't work. Also, no cast: jsonb_build_object() already returns jsonb.

④ My guess. What you had, didn't work. Note the use of the EXCLUDED key word to refer to the row initially proposed for insertion.

Upsert on a table that has multiple fields and jsonb column using WITH statement

1 Answers1

Linked