upsert-example.sql

DROP TABLE warehouse.test_main_table;
CREATE TABLE warehouse.test_main_table (
  pk int PRIMARY KEY
  , main_col int 
  , data_json JSONB
);

DROP TABLE warehouse.test_temp_table;
CREATE TABLE warehouse.test_temp_table (
  pk int PRIMARY KEY 
  , temp_col int
  , data_json text
);

INSERT INTO warehouse.test_main_table
  VALUES (1, 77, '{"b":1}');

INSERT INTO warehouse.test_temp_table
  VALUES (1, 11, '{"a":1}'), (2, 22, '{"a":2}');

SELECT * FROM warehouse.test_main_table;
SELECT * FROM warehouse.test_temp_table;

INSERT INTO warehouse.test_main_table (pk, main_col, data_json) 
  (SELECT pk, main_col, data_json::JSON FROM warehouse.test_temp_table)
  ON CONFLICT (pk) DO UPDATE
    SET main_col = EXCLUDED.main_col, data_json = EXCLUDED.data_json;