1

I'm using NodeJS and pg with a PostgreSQL database as backend. Running single queries with Client.query works fine.

However, I've got a challenge which I can solve in a simple and elegant way.

I would like to run:

UPDATE portfolios SET votes = $1 WHERE id = $2

from an array/list of:

[{votes: 5, id: 1}, {votes: 15, id: 1}, {votes: 25, id: 2}]

Can it be done in a single Client.query (or similar with pg), so I don't have to make a "for () { ... }"?

Michael Nielsen
  • 1,194
  • 3
  • 22
  • 37
  • Duplicate of [Update multiple rows in same query using PostgreSQL](http://stackoverflow.com/questions/18797608/update-multiple-rows-in-same-query-using-postgresql). – vitaly-t May 05 '16 at 15:39

1 Answers1

2

The driver must pass the query to Postgresql in this final shape:

update portfolios p
set votes = s.votes
from unnest(array[(5, 1), (15, 1), (25, 2)]) s (votes int, id int)
where p.id = s.id

So pass this query to the driver:

update portfolios p
set votes = s.votes
from unnest($1) s (votes int, id int)
where p.id = s.id

with an appropriate array as parameter.

Notice that if you string build it yourself in instead of letting the driver do it you will be vulnerable to SQL injection.

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • I see. Is there an easy way to convert "[{votes: 5, id: 1}, {votes: 15, id: 1}, {votes: 25, id: 2}]" into a correct array, as it seems UNNEST doesn't accept list of objects? – Michael Nielsen May 05 '16 at 15:12
  • It seems as if I'm not allowed to map the array for returning tuples: my_array = my_array.map(function(e) { return tuple(e.votes, e.id); }) – Michael Nielsen May 05 '16 at 15:22
  • Did you ever figure out how to solve your problem with the tuples in JS? – Ayudh Jan 12 '22 at 09:41