Postgresql Insert and Update JSONB Column

Question

Would someone please show me how to do insert & update a jsonb column? Say I have this Permanent table that has 2 columns (ticker varchar(10), data(jsonb)), with ticker is the primary key: In it, it has this data

XYZ,{"sales":{"2022-06-30":1500,"2022-03-31":1200}}

Now, a new quarter has been reported and the Temp table has this data:

XYZ,{"sales":{"2022-09-30":1800}}

How can I do INSERT INTO so it update just the data(jsonb)

INSERT INTO perm_table(ticker,data)
FROM temp_table ticker,data
ON CONFLICT ????? DO NOTHING;

Thank you so much in advance for your guidance!

Barbaros Özhan · Accepted Answer · 2022-12-29 14:00:00Z

1

One option is to use a Common table expression(or possibly a subquery) after outer full joining tables while reconstructing the objects and completing missing values through use of COALESCE() such as

WITH j AS
(
  SELECT id_, jsonb_build_object('sales', js) AS js
    FROM (SELECT COALESCE(t.id, p.id) AS id_,
                 jsonb_object_agg(COALESCE((js_t).key, (js_p).key),
                                  COALESCE((js_t).value, (js_p).value)) AS js
            FROM (SELECT id, jsonb_each(val) AS js_t
                    FROM temp_table
                   CROSS JOIN jsonb_each(data) AS arr(key, val)) AS p
            FULL JOIN (SELECT id, jsonb_each(val) AS js_p
                        FROM perm_table
                       CROSS JOIN jsonb_each(data) AS arr(key, val)) AS t
              ON (js_p).key = (js_t).key
           GROUP BY id_) AS q
)
UPDATE perm_table
   SET data = j.js
  FROM j 
 WHERE id = id_

Demo

answered Dec 29, 2022 at 14:00

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TimB Over a year ago

Mr. Ozhan, I am grateful for your taking the time to teach me again! This coding is wonderful. I would never be able to come up with it myself. Thank you!!!!

Collectives™ on Stack Overflow

Postgresql Insert and Update JSONB Column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related