5

I am trying to insert multiple rows to pgsql database using pg-promise. In my case, few records, which I am trying to insert, may exist already in the table. In such case, I need to update them. By going through the pg-promise official documentaion, I found multiple rows can either be inserted or updated using helpers. Is there any way to do like below but for multiple inserts/updates?

INSERT INTO table_name(column_list) 
VALUES(value_list)
ON CONFLICT target action;
Bergi
  • 630,263
  • 148
  • 957
  • 1,375
Giri
  • 451
  • 1
  • 4
  • 13
  • 1
    Yes, just create the insert-values query using helpers, then tack on the `ON CONFLICT …` as a string. – Bergi Aug 09 '20 at 14:33
  • @Bergi Yes, pretty much. I added my answer anyhow, might help a bit more :) – vitaly-t Aug 09 '20 at 18:38
  • @vitaly-t Yeah, but I didn't know from the top of my head how it would look exactly, and I didn't know about `assignColumns`! – Bergi Aug 09 '20 at 18:50

1 Answers1

8

Create your static variables somewhere:

const cs = new pgp.helpers.ColumnSet(['first', 'second', 'third', 'fourth'], 
                                      {table: 'my-table'});

// let's assume columns 'first' and 'second' produce conflict when exist:
const onConflict = ' ON CONFLICT(first, second) DO UPDATE SET ' +
    cs.assignColumns({from: 'EXCLUDED', skip: ['first', 'second']});

In the example below we assume your data is an array of valid objects:

const upsert = pgp.helpers.insert(data, cs) + onConflict; // generates upsert

await db.none(upsert); // executes the query:

Extras

If you want all SQL generated in upper case rather than low case, you can set option capSQL:

const pgp = require('pg-promise')({
  capSQL: true
});

And the best way to see what's generated and executed is with the help of pg-monitor.

vitaly-t
  • 24,279
  • 15
  • 116
  • 138
  • What if your update is dependent on current value? Like first = my-table.first + EXCLUDED.first Should you just add it part of the onConflict query or can you do something with assignColumns? – Marius Mircea Aug 30 '21 at 19:58
  • 1
    @MariusMircea You would just have to append that manually, `assignColumns` is there only to simplify regular assignment, because that covers 99% of all practical cases. And what you are asking is fairly unique. – vitaly-t Aug 30 '21 at 20:15
  • 1
    Works perfectly! For anyone like me who was confused at the use of 'EXCLUDED', it is a postgres syntax for accessing the row that we were unsuccessful in inserting https://www.postgresql.org/docs/current/sql-insert.html – anotherfred Feb 07 '22 at 12:31
  • Is there any method to have the count of rows updated and the count of rows created returned? I see using `db.result()` we receive `rowCount` however nothing to specify columns which hit the conflict. – user3770935 Apr 26 '22 at 07:57
  • thanks mate this is gold! I have never worked with pg but this is awesome! – B.Ramburn Mar 19 '23 at 14:23