Pg-promise: Support ON CONFLICT clause for bulk inserts

Created on 27 Aug 2018 · 1Comment · Source: vitaly-t/pg-promise

While https://github.com/vitaly-t/pg-promise/wiki/Data-Imports makes massive inserts a breeze. However, I've run frequently in cases where the bulk insert fails because of conflicts (e.g., when filling in data from export).

Postgres supports setting ON CONFLICT for bulk inserts to help to deal with these cases.

It doesn't seem to be possible to set on conflict clause for the inserts without manually patching the module source.

It would be nice to see first-class support for ON CONFLICT to land here.

question

Source

jamo

Most helpful comment

@jamo You simply append the ON CONFLICT part to your query, and that's it.

For example, if you are getting a conflict on columns col1 + col2, you would often use:

const insert = pgp.helpers.insert(data, cs) + ' ON CONFLICT(col1, col2) DO UPDATE SET ' +
    cs.assignColumns({from: 'EXCLUDED', skip: ['col1', 'col2']});

See:

vitaly-t on 27 Aug 2018

👍3

>All comments

@jamo You simply append the ON CONFLICT part to your query, and that's it.

For example, if you are getting a conflict on columns col1 + col2, you would often use:

const insert = pgp.helpers.insert(data, cs) + ' ON CONFLICT(col1, col2) DO UPDATE SET ' +
    cs.assignColumns({from: 'EXCLUDED', skip: ['col1', 'col2']});

See:

vitaly-t on 27 Aug 2018

👍3

Was this page helpful?

0 / 5 - 0 ratings