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?

• Craig Ringer: "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?
• user2627000: It's 9.3 beta's function

Answer 1

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.

• jney: very cool. is there a way to UPDATE too, based on a json input ?
• Craig Ringer: @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: 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: 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.

Answer 2

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"}');

• David Tedman-Jones: 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: 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.