node-postgres: Update more than one record at once

Question

I'm building an app in node.js using node-postgres. I don't know how to update more than one record at once.
Here is my code:

var status = 20;
var id = [23,12,43];

pool.connect(function(err, client, done) 

{


if(err) 
{
      done();
      console.log(err);
      return res.status(500).send(json({ success: false, data: err}));
}
    client.query("UPDATE myDatabase SET status=($1) WHERE id IN($2);", [status,id]);

But I've got a problem - console:

events.js:160 throw er; // Unhandled 'error' event ^

error: invalid input syntax for integer: "23,12,43"

What can I do in that case? What kind of data use? the length of var id will be not the same every time.

Greetings, Rafał
EDIT: I think that a change of data will work in javascript, the input must be 23,12,43, but instead is [23,12,43] but I don't know how to make that.

Are you using `node-postgres` directly or via [pg-promise](https://github.com/vitaly-t/pg-promise)? — vitaly-t, Jul 28 '16 at 14:59
I've just saw pg-promise and it's looking nice, I will definietly try that — Rafalsonn, Jul 29 '16 at 00:29
with `pg-promise` you can format id-s in your query like this: `WHERE id = ANY($2:csv)`. — vitaly-t, Jul 29 '16 at 03:21

score 1 · Accepted Answer · answered Jul 28 '16 at 13:39

1

It looks like you want to use ANY:

client.query("UPDATE myDatabase SET status=($1) WHERE id = ANY($2);", [status,id]);

This is due Postgresql itself, not the library: see this and this

See also this question: Oracle: '= ANY()' vs. 'IN ()'

answered Jul 28 '16 at 13:39

rpadovani

7,101
2
31
50

node-postgres: Update more than one record at once

1 Answers1