I am trying to do a "generic" insert/update of table columns using JSON. There are tons of info how to update a JSON column, but I have found none that updates several normal columns in a table generic.

Insert columns with JSON data works

This works as I understand it in a generic way:

INSERT INTO inputtable
FROM json_populate_record (NULL::inputtable,
      "id": "0",
      "name": "orkb type foo examples tutorials orkb",
      "sum": 5743,
      "float_col": 94.55681687716474

Fiddle: https://www.db-fiddle.com/f/eaQG8H4yqY9hnQBZjzJgz/29

Update columns with JSON data?

There are examples of "non-generic" update using JSON, but I am searching for a "generic" solution. In my dreams it should work as this pseudo code:

with data as (
select * from json_each_text(
      "id": "0",
      "name": "orkb type foo examples tutorials orkb",
      "sum": 5743,
      "float_col": 95
update inputtable set = (select * from data) where id=0

Is this possible to do? If it is, how do I do this?


I have found no other way than hardcode the update query

update inputtable
    (id, name, sum, float_col) =
    (j.id, j.name, j.sum, j.float_col)
from jsonb_populate_record ( null::inputtable,
      "id": "1",
      "name": "test",
      "sum": 5432,
      "float_col": 94.1
) j;

select * from inputtable;

Fiddle: https://www.db-fiddle.com/f/eaQG8H4yqY9hnQBZjzJgz/36

Though I found that PostgREST may doing it another way. They claim that they not are an ORM magic. But how do they do this?

PATCH /table_name?myPKey=eq.thePKey
{ "col1":"update-value1", "col2":"update-value2" }
  • You could write a routine using dynamic sql based on the results of json_each_text. Only go there if you have enough CPU power to spare. Jul 20, 2021 at 10:55
  • You mean a function? Any tip or link that gives a clue?
    – sibert
    Jul 20, 2021 at 11:16
  • "dynamic sql" was meant to be the tip. To be used perhaps in a pre-insert trigger, or instead-of-insert trigger if Postgresql knows these. Jul 20, 2021 at 12:01
  • 1
    You have to specify the individual columns in the SET clause, there is no way around that. Jul 20, 2021 at 12:54
  • 1


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.