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?

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.

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