PostgreSQL: insert data into table from json
Solution 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 aVALUES
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.
Solution 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"}');
Related videos on Youtube
user2627000
Updated on January 24, 2020Comments
-
user2627000 about 4 years
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 over 10 years"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?
-