Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2005
    Posts
    2

    Unanswered: Ignoring duplicate entries

    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.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    This works on other DBMS, so we'd like to keep the code working that way it is.
    Which DBMS is that? The definition of a transaction is, that either all statements or none are made permanent ("committed") but never parts of the transaction

    The only thing I can think of, is to remove the unique index/PK and insert the records as they come, and then later remove the duplicates.

  3. #3
    Join Date
    Aug 2005
    Posts
    2
    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.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    OK, I see. This is a "feature" of PG's JDBC driver that annoys me as well. I don't know of any way to avoid this other then setting AutoCommit to false.

    But you might want to send this to the Postgres JDBC mailing list, the developers of the driver do monitor that, and will be able to give you more detailed answers.

    http://www.postgresql.org/community/lists/

    Regards
    Thomas

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •