How to insert into a table with not null columns using json?

Question

I am trying to insert into a table food with multiple not-null default columns, with commands like:

food_insertone('{"id": 1, "taste": "sweet"}'::JSON)
food_insertone('{"id": 2}'::JSON)
food_insertone('{"id": 3, "taste": null}'::JSON)

And the result should be like:

INSERTED 1, 'sweet'
INSERTED 2, ''
ERROR (null not allowed in taste)

The table food is defined as:

CREATE TABLE "food" (
  "id"    INT,
  "taste" TEXT NOT NULL DEFAULT '',
  ...
);

CREATE OR REPLACE FUNCTION "food_insertone" (JSON)
RETURNS VOID AS $$
  INSERT INTO "food" SELECT * FROM json_populate_record(NULL::"food", $1);
$$ LANGUAGE SQL;

And i am trying to insert as:

SELECT food_insertone('{"id": 1}'::JSON);

But this doesnt work and gives me an error:

null value in column "taste" violates not-null constraint

I understand that json_populate_record() creates NULL values for columns which are not mentioned in the JSON, which is causing NULL to be inserted, and thus this error. A plain insert would work, but this is a dynamic table.

With plain insert, i meant something like INSERT INTO "food" (id) VALUES (1) for this particular case, but table has multiple such column. — wolfram77
– wolfram77, Commented Sep 25, 2017 at 12:44
I just want to use default value if it is not specified in the json, but still not allow null. — wolfram77
– wolfram77, Commented Sep 25, 2017 at 12:47
Would it be possible to concat the row values from json_populate_record() with a row of default values of the table? — wolfram77
– wolfram77, Commented Sep 25, 2017 at 12:55

Vao Tsun · Accepted Answer · 2017-09-25 13:18:01Z

2

to use the default value simple case:

t=# create table food(id int, t text not null default 'some');
CREATE TABLE
t=# insert into food(id) SELECT id FROM json_populate_record(NULL::"food", '{"id":0}');
INSERT 0 1
t=# select * from food ;
 id |  t
----+------
  0 | some
(1 row)

using coalesce and another value:

t=# insert into food(id,t) 
SELECT id,coalesce(t,'some simple other value') 
FROM json_populate_record(NULL::"food", '{"id":0}');

and of course you can use some monstrous way to get actual default value in :

t=# insert into food(id,t) SELECT id,coalesce(t,rtrim) FROM json_populate_record(NULL::"food", '{"id":0}') join (select rtrim(ltrim(split_part(column_default,'::',1),$$'$$),$$'$$) from information_schema.columns where table_name = 'food' and column_name = 't') dflt on true;
INSERT 0 1
t=# select * from food ;
 id |            t
----+-------------------------
  0 | some simple other value
  0 | some
(2 rows)

edited Sep 25, 2017 at 13:18

answered Sep 25, 2017 at 13:00

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

7 Comments

wolfram77 Over a year ago

But i also want to insert the value of t if its provided (as long as it is not null).

wolfram77 Over a year ago

i think your monstrous way is the best because i dont know either the columns or thier default values before hand (i am using alter to add columns). but i couldnt understand what it does, could you explain?

wolfram77 Over a year ago

i think a got another monstrous method: 1. to get default values as json (

SELECT json_object_agg(column_name, column_default)::JSONB FROM information_schema.columns WHERE (table_schema, table_name) = ('public', 'food');

).

wolfram77 Over a year ago

2. i could then append this to the original input json and do json_populate_record(), that should work. thanks.

wolfram77 Over a year ago

i think you put some extra $ which is not part of the command. i see it was needed to remove the type cast. i am using this query now and it works:

CREATE OR REPLACE FUNCTION "table_default" (TEXT) RETURNS JSON AS $$   SELECT json_object_agg(column_name, replace(split_part(column_default,'::',1), E'\'', ''))   FROM information_schema.columns   WHERE (table_schema, table_name) = ('public', $1); $$ LANGUAGE SQL STRICT IMMUTABLE;

.

|

Collectives™ on Stack Overflow

How to insert into a table with not null columns using json?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related