how do I insert many, if it already exists, dont do anything with PG-promise

Question

The error: duplicate key value violates unique constraint "users_pkey"

I am using Node and PG-Promise

I am bulk inserting a bunk of data into my database. Sometimes, the data already exists so I need to either overwrite it or just ignore that row.

I saw that I can do this:

INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE    

But I can't seem to figure out how to do that with PG-Promise when I am bulk inserting.

let users = [...]
const query = pgp.helpers.insert(users, userTable)

What would the syntax look like?

Captivate

https://mcmap.net/q/332102/-trying-to-constrain-put-request-to-update-if-unique-for-key-postgresql/5079799 – Disfeature

Answer 1

According to https://github.com/vitaly-t/pg-promise/issues/542,

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

So in your case

const users = […];
const query = pgp.helpers.insert(users, cs) + ' ON DUPLICATE KEY UPDATE';

Where cs is your ColumnSet object. You also will typically use assignColumns method, as you can see from the same link.

Notice that the proper Postgres INSERT syntax uses ON CONFLICT DO UPDATE ….

Tourneur

Yep, just about, but perhaps he will need to use ON CONFLICT(col1, col2) DO UPDATE SET syntax instead. I also added the assignColumns reference. – Theola