PostgreSQL: insert data into table from json

Question

Now I use to manually parse json into insert string like so

insert into Table (field1, field2) values (val1, val2)

but its not comfortable way to insert data from json! I've found function json_populate_record and tried to use it:

create table test (id serial, name varchar(50));
insert into test select * from json_populate_record(NULL::test, '{"name": "John"}');

but it fails with the message: null value in column "id" violates not-null constraint PG knows that id is serial but pretends to be a fool. Same it do for all fieds with defaults.

Is there more elegant vay to insert data from json into a table?

"I found function json_populate_record". Um. Where? What's the function definition? Are you running PostgreSQL 9.3 beta, or is it a function you installed from somewhere else? — Craig Ringer
– Craig Ringer, Commented Jul 28, 2013 at 11:32

Craig Ringer · Accepted Answer · 2013-07-28 13:34:31Z

18

There's no easy way for json_populate_record to return a marker that means "generate this value".

PostgreSQL does not allow you to insert NULL to specify that a value should be generated. If you ask for NULL Pg expects to mean NULL and doesn't want to second-guess you. Additionally it's perfectly OK to have a generated column that has no NOT NULL constraint, in which case it's perfectly fine to insert NULL into it.

If you want to have PostgreSQL use the table default for a value there are two ways to do this:

Omit that row from the INSERT column-list; or
Explicitly write DEFAULT, which is only valid in a VALUES expression

Since you can't use VALUES(DEFAULT, ...) here, your only option is to omit the column from the INSERT column-list:

regress=# create table test (id serial primary key, name varchar(50));
CREATE TABLE
regress=# insert into test(name) select name from json_populate_record(NULL::test, '{"name": "John"}');
INSERT 0 1

Yes, this means you must list the columns. Twice, in fact, once in the SELECT list and once in the INSERT column-list.

To avoid the need for that this PostgreSQL would need to have a way of specifying DEFAULT as a value for a record, so json_populate_record could return DEFAULT instead of NULL for columns that aren't defined. That might not be what you intended for all columns and would lead to the question of how DEFAULT would be treated when json_populate_record was not being used in an INSERT expression.

So I guess json_populate_record might be less useful than you hoped for rows with generated keys.

answered Jul 28, 2013 at 13:34

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

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

4 Comments

jney Over a year ago

very cool. is there a way to UPDATE too, based on a json input ?

Craig Ringer Over a year ago

@jney I'm not really sure what you're asking. I suggest posting a new properly detailed question and linking back to this one for context.

beldaz Over a year ago

Very useful information (documentation for json_populate_record is a bit sparse). But there really does seem to be a good case for an option to the effect of "where no value specified, use the default".

ps2goat Sep 22 at 20:49

I have a few scenarios where I did something like this (pg 15 and 17), but I make a json object with the columns that should have a default in the column. I set those and then merge the two json objects first, then populate the record. This was limited to id and created timestamp type columns where the values should not be coming from the client. It still would be nice to get away from having to set those column values specifically.

Community · Accepted Answer · 2017-05-23 12:02:04Z

14

Continuing from Craig's answer, you probably need to write some sort of stored procedure to perform the necessary dynamic SQL, like as follows:

CREATE OR REPLACE FUNCTION jsoninsert(relname text, reljson text)
  RETURNS record AS
$BODY$DECLARE
 ret RECORD;
 inputstring text;
BEGIN
  SELECT string_agg(quote_ident(key),',') INTO inputstring
    FROM json_object_keys(reljson::json) AS X (key);
  EXECUTE 'INSERT INTO '|| quote_ident(relname) 
    || '(' || inputstring || ') SELECT ' ||  inputstring 
    || ' FROM json_populate_record( NULL::' || quote_ident(relname) || ' , json_in($1)) RETURNING *'
    INTO ret USING reljson::cstring;
  RETURN ret;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

Which you'd then call with

SELECT jsoninsert('test', '{"name": "John"}');

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered Feb 17, 2015 at 23:11

beldaz

4,5413 gold badges49 silver badges65 bronze badges

2 Comments

David Tedman-Jones Over a year ago

This is exactly what I've been looking for! Saved after many hours of searching. If I wanted the return value to be in json object form, just as in the input value, how would this be achieved?

beldaz Over a year ago

Glad it helped. If you wanted to return the JSON, I guess change to RETURNS json and instead or RETURN ret try RETURN row_to_json(ret). I haven't tested, but that's the general idea.

Collectives™ on Stack Overflow

PostgreSQL: insert data into table from json

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related