pg-promise number of updated rows

Question

I am using the pg-promise NodeJS module. At some point, I am updating one of my database's table. I would like to know how many rows have been updated by the query, is it possible?

Below is part of my code:

var queryText = "UPDATE public.mytable SET whatever = $1 WHERE criteria1=$2 AND criteria2=$3;",
    queryParam = [
      value1,
      value2,
      value3
    ];

    postgres.none(queryText, queryParam)
    .then((value) => {
      // how can I know at this point how many rows have been updated
      resolve(result);

    })
    .catch(err => {
      // it doesn't go here when zero row has been updated
      reject(err);
    });

Thank you!

vitaly-t · Accepted Answer · 2020-09-26T19:50:03.073

25

I'm the author of pg-promise. In order to access the advanced query-result details, you need to execute the query via method result. And in your case you would access property rowCount:

db.result(query, values, r => r.rowCount)
    .then(count => {
       // count = number of rows affected (updated or deleted) by the query
    })
    .catch(error => {
    });

P.S. You do not need to use resolve(result) inside .then or reject(err) inside .catch, as all query methods of pg-promise already return promises.

UPDATE

Newer, ES7 syntax:

const {rowCount} = await db.result(query, values);

edited Sep 26 '20 at 19:50

answered Jun 26 '17 at 15:30

vitaly-t

24,279
15
116
138

If you're in an `async` function, you can also do e.g. `let result = await db.result('DELETE FROM mytable WHERE criteria1 = $1', ['foo'])` – Dave Gray Oct 11 '19 at 15:30
1

@DaveGray That is self-evident when one moves from ES6 to ES7 syntax. At least you could have used the example correctly, with the transformation `r => r.rowCount` argument, or the example won't work otherwise. – vitaly-t Oct 11 '19 at 15:46
1

You're right, it's not an exact drop-in replacement without doing `let result = await db.result('DELETE FROM mytable WHERE criteria1 = $1', ['foo'], r => r.rowCount)` - apologies to OP; in my use case I wanted to see the entire result object. Thanks for the library, it has saved me lots of time! – Dave Gray Oct 16 '19 at 22:14
Added ES7 example. – vitaly-t Sep 26 '20 at 19:51
when used with insert on conflict do update, how do I find out how many rows it inserted vs updated? – PirateApp Aug 25 '21 at 17:20
1

@PirateApp You can't. Postgres can only report how many rows have been affected/modified, without any specifics. – vitaly-t Aug 25 '21 at 19:01

pg-promise number of updated rows

1 Answers1