unique constraint violation causes entire pq.CopyIn postgresql import to fail

Question

I'm trying to use pq.CopyIn to do bulk imports as described here:

The import is much faster than other methods I've tried but I am finding that a unique constraint violation in just one record will cause the entire import to fail.

Is there any way to change this behavior? Is there some easy way to find out which record caused the problem? Is there a better option that pq.CopyIn for fast imports?

For my application I could do some queries and some checking of the data that I'm importing but I'm hoping there's a better way.

score 0 · Answer 1 · answered Jun 07 '18 at 05:03

0

The error message should give you a clue where the problem is.

Since the COPY statement is (like all SQL statements) running in its own transaction, a single error will roll back the whole work.

If you are willing to live with slower processing, you can resort to INSERT ... ON CONFLICT DO NOTHING.

answered Jun 07 '18 at 05:03

Laurenz Albe

209,280
17
206
263

Error message is `pq: duplicate key value violates unique constraint`. No indication of which record failed. – JeffB Jun 07 '18 at 16:44
Seeps like pq hides the context and other details of the error message. – Laurenz Albe Jun 07 '18 at 17:11

unique constraint violation causes entire pq.CopyIn postgresql import to fail

1 Answers1