How to Insert into with postgresql and JSONB

Question

I am using Postgresql 9.5

I have two tables(product and product_temp) with jsonb colums

I would like to insert the values from product_temp to product

but the name of the data inside the json is not equal ex:

INSERT INTO product(data1->>'__COD__', data1->>'__PAGE__', job_id) 
 SELECT data2->>'cod', data2->>'page', job_id 
 FROM product_temp where someid = '123'

Answer 1

Quickly did lots of testing and i got what you wanted.

INSERT INTO product(data1, job_id)
SELECT json_build_object('__COD__',data2->>'cod','__PAGE__',data2->>'page') data1, job_id FROM product_temp;

Your data1 is one column. Tested with PostgreSQL 9.5

• ricardo: OW Perfect answer!