INSERT ON CONFLICT DO UPDATE using pg-promise helpers for multi row insert/update

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;

Answer

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']});

• See ColumnSet and assignColumns.

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.