Batch inserts with JDBC and Unique constraint

Question

I have a working method in Java that inserts many rows as a batch.

The issue I'm facing is that I have a unique constraint on my table and if there are some rows that are already in the table the method does not insert the ones that do not belong in the database. Instead it throws the following exception

java.sql.BatchUpdateException: Batch entry 0 INSERT INTO .... was aborted.  Call getNextException to see the cause.

Is there anything I can do to make the method insert the ones that are not already in table and not abort like it's doing now?

Can you perhaps change the SQL in the command from a plain insert to an insert with a where condition that checks that a record with the given field value doesn't exist? — RealSkeptic, Jun 25 '15 at 19:00
that could work, I have to make the sql statement to see how well it would work — Arya, Jun 25 '15 at 19:01
unfortunately postgresql does not support merge or upsert yet — Alexander Kudrevatykh, Jun 25 '15 at 19:28
You appear to want a bulk upset operation. http://stackoverflow.com/q/17267417/398670 — Craig Ringer, Jun 26 '15 at 02:34

score 0 · Answer 1 · answered Jun 25 '15 at 18:54

0

You can handle this stuff through before insert trigger on that table.

answered Jun 25 '15 at 18:54

Raman Shrivastava

2,923
15
26

I was doing that before but with thousands of rows it slows down. There are no alternatives? – Arya Jun 25 '15 at 18:55
Based upon which field or combination of fields do you decide if that row is already their in db? Before your batch insert, you can get those through a prepared statement in java, then compare (and whatever logic you need), and send only those rows through which won't fail. – Raman Shrivastava Jun 25 '15 at 18:58
Only if you `LOCK` the table first. – Craig Ringer Jun 26 '15 at 02:34

Batch inserts with JDBC and Unique constraint

1 Answers1