Sql Continue Executing Queries After Duplicate Key Violation

I have a situation where I want to insert a row if it doesn't exist, and to not insert it if it already does. I tried creating sql queries that prevented this from happening (see h

Solution 1:

I think what you could do is use a SAVEPOINT before trying to execute the statement which could cause the violation. If the violation happens, then you could rollback to the SAVEPOINT, but keep your original transaction.

Here's another thread which may be helpful: Continuing a transaction after primary key violation error

Solution 2:

I gave an up-vote to the SAVEPOINT answer--especially since it links to a question where my answer was accepted. ;)

However, given your statement in the comments section that you expect errors "more often than not," may I suggest another alternative?

This solution actually harkens back to your other question. The difference here is how to load the data very quickly into the right place and format in order to move data around a single SELECT -and- is generic for any table you want to populate (so the same code could be used for multiple different tables). Here's a rough layout of how I would do it in pure PostgreSQL, assuming I had a CSV file in the same format of the table to be inserted into:

CREATE TEMP TABLE input_file (LIKE target_table);

COPY input_file FROM'/path/to/file.csv'WITH CSV;

INSERTINTO target_table
SELECT*FROM input_file
WHERE (<unique key field list>) NOTIN (
    SELECT<unique key field list>FROM target_table
);

Okay, this is a idealized example and I'm also glossing over several things (like reporting back the duplicates, pushing the data into the table via Python in-memory data, COPY from STDIN rather than via a file, etc.), but hopefully the basic idea is there and it's going to avoid much of the overhead if you expect more records to be rejected than accepted.