Anyone know how to get Postgres to ignore entries that violate primary key constraints instead of raising an error?
Our application accesses the database using JDBC. The table is getting 100-1000 entries added to it every second. To get that kind of performance, we had to set auto commit to false and perform a commit when a batch of entries have been added. The problem is that there will be duplicates coming in, and when one entry fails the primary key constraint, the rest of them fail as well (duplicate or not). Can we get the duplicates to fail silently or at least not cause the whole transaction to fail?
This works on other DBMS, so we'd like to keep the code working that way it is.
Sorry for the late reply. Other issues which are more pressing have come up.
By works, I meant that an SQLException would be thrown for that particular entry, but other entries could be added after an offending one. The other DBMS that let us get away with this are MySQL and SQL Server. I know it doesn't fit the definition of a transaction, but it worked.
I was hoping for something like SQL Server's "IGNORE_DUP_KEY" option on creating an index. Removing the PK isn't really an option since there isn't enough downtime to remove the duplicates on several million rows.