How to update several rows with postgresql function

Question

I have a table representing an object:

id|field1|field2|field3
--|------|------|------
1 |   b  |  f   |  z
2 |   q  |  q   |  q

I want to pass several objects to pg function which will update corresponding rows. Now I see only one way to do it - to pass a jsonb with array of objects. For example: [{"id":1, "field1":"foo", "field2":"bar", "field3":"baz"},{"id":2, "field1":"fooz", "field2":"barz", "field3":"bazz"}]
Is this a best way to perform an update? And what is the best way to do it with jsonb input? I don't really like the way to convert jsonb input to rows with select * from json_each('{"a":"foo", "b":"bar"}') and operating it. I would prefer some way to execute a single UPDATE.

Please add your table schema, sample data with code what you have tried so far — Akhilesh Mishra
– Akhilesh Mishra, Commented May 28, 2021 at 19:23

Harsh Gundecha · Accepted Answer · 2021-05-28 16:07:51Z

1

this can be achieved using from clause in update along with a dynamic on the fly table from input as follows assuming that the DB table and custom input will be mapped/matched with each other on basis of ID

  update table as t1
  set field1 = custom_input.field1::varchar, 
      field2 = custom_input.field2::varchar, 
      field3 = custom_input.field3::varchar
  from (
      values 
          (1, 'foo', 'bar', 'baz'), 
          (2, 'fooz', 'barz', 'bazz')
  ) as custom_input(id, field1, field2, field3) 
  where t1.id = custom_input.id::int;

edited May 28, 2021 at 16:07

answered May 28, 2021 at 16:02

Harsh Gundecha

1,23511 silver badges16 bronze badges

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

2 Comments

SuperSonic Over a year ago

Thx. I'd like to use functions and this cause problems with passing arguments. Now I have same function as you proposed but with select id, field1, field2, field3 from json_each(in_jsonb) in from clause. I was wondering if there is a single-line update query with jsonb

Harsh Gundecha Over a year ago

This query is usually executed by dynamically buiding the query at runtime by manipulating strings which is easy to do apart from that it Would be easier to help if you can setup db fiddle with tables and sample data

Slava Rozhnev · Accepted Answer · 2021-05-30 08:30:32Z

0

You can do it in next way (using json_populate_recordset):

update test
set 
    field1 = data.field1,
    field2 = data.field2,
    field3 = data.field3
from (select * from json_populate_recordset(
  NULL::test, 
  '[{"id":1, "field1":"f.1.2", "field2":"f.2.2", "field3":"f.3.2"},{"id":2, "field1":"f.1.4", "field2":"f.2.5", "field3":"f.3.6"}]'
)) data
where test.id = data.id;

PostgreSQL fiddle

answered May 30, 2021 at 8:30

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

Collectives™ on Stack Overflow

How to update several rows with postgresql function

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related