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



  • 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



  • 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 https://www.postgresql.org/docs/current/sql-insert.html .

    ③ 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.




Suggested Topics

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