postgresql insert into select from json

Question

Is it possible to insert into a table from a json dictionary in PostgreSQL without specifying the columns?

Given there is a table books_1:

CREATE TABLE books_1 ( data json );
INSERT INTO books VALUES ('{ "name": "Book the First", "author": "Bob White" }');
INSERT INTO books VALUES ('{ "name": "Book the Second", "author": "Charles Xavier" }');
INSERT INTO books VALUES ('{ "name": "Book the Third", "author": "Jim Brown" }');

Which I want to insert into a table that has the exact same columns and datatypes, such as:

CREATE TABLE books_2 ( name varchar(100), author varchar(100) );

Ideally I would execute:

INSERT INTO books_2
SELECT something_json(data) FROM books_1

Background: Using standard PostgreSQL I can easily make JSON table dumps. I am also able to import them, but they are imported as jsonb.

COPY ( SELECT ROW_TO_JSON(t) FROM (SELECT * FROM books_2) t) TO 'C:\Users\Public\books.json';

CREATE TABLE books_1 ( j jsonb );
COPY t FROM 'C:\Users\Public\books.json';

I want to re-import the json into 'regular' attributes, not json. I could do this by specifiying all the columns, but since there are a lot of tables and columns I really don't want to do that. There should be no need as the columns are the same anyway.

I'm probably looking for a function that returns a list of columns based on the keys in the json. I guess the order would have to be the same. But since the export is also done from PostgreSQL that is probably guaranteed.

Why not simply export into a format that copy can process natively? — user330315
– user330315, Commented Dec 1, 2019 at 13:24
Reasons to use json is that I or others can easily change the data and store it under version control such as git. For the latter the format doesn't really matter, but for humans to update data the first is quite handy. — svenema
– svenema, Commented Dec 1, 2019 at 13:30

Bergi · Accepted Answer · 2019-12-01 13:46:10Z

1

You can use json_populate_record with the rowtype of the target table:

INSERT INTO books_2
SELECT rec.* FROM books_1, json_populate_record(null::books_2, books_1.data) rec;

(online demo)

edited Dec 1, 2019 at 13:46

answered Dec 1, 2019 at 13:39

Bergi

670k162 gold badges1k silver badges1.5k bronze badges

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

1 Comment

svenema Over a year ago

Perfect. Funny thing, I was trying this, but used the wrong version jsonb vs. json of this function. Thanks a lot!

Collectives™ on Stack Overflow

postgresql insert into select from json

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related