CODEWITHSUNDEEP
node.jspostgresqlpg-promise
Syn
Syn

Reputation: 1128

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

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?

Upvotes: 1

Views: 1195

Answers (1)

Bergi
Bergi

Reputation: 664548

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 ….

Upvotes: 2

Related Questions