0

I want to know what is the optimal approach for inserting a duplicate record. I am using Postgres. I am trying 2 approaches and have timing metrics on the results but I would like to know which is best or if there is another way.

  1. Perform INSERT then if there is a duplicate key exception, I handle that.
  2. Before performing INSERT do a SELECT, if the record exists I will not perform the INSERT.

I feel approach 1 is best as there is only one request to the DB but if there are many duplicates then approach 1 is quite slow, due to the duplicate key exception handling I assume.

In my use case, I am expecting round 3-4% duplicates.

mrmannione
  • 749
  • 10
  • 29
  • 3
    Why not simply use `insert on conflict`? –  Oct 12 '18 at 10:13
  • Can you give an example. Here is my current: INSERT INTO objects (org, space, participant) VALUES ('584cf4cd-eea7-4c8c-83ee-67d88fc6eab5', 'aeb08f71-710c-43c2-b62e-5a644925316e', 'fa56e002-ff83-11e7-99ae-a1d83457a777') – mrmannione Oct 12 '18 at 10:19
  • 1
    Just add `on conflict do nothing` https://www.postgresql.org/docs/current/static/sql-insert.html#SQL-ON-CONFLICT –  Oct 12 '18 at 10:21
  • INSERT INTO objects (org, space, participant) VALUES ('584cf4cd-eea7-4c8c-83ee-67d88fc6eab5', 'aeb08f71-710c-43c2-b62e-5a644925316e', 'fa56e002-ff83-11e7-99ae-a1d83457a777') ON CONFLICT getting org.springframework.jdbc.BadSqlGrammarException – mrmannione Oct 12 '18 at 10:24
  • 1
    you may refer this link : https://stackoverflow.com/questions/17267417/how-to-upsert-merge-insert-on-duplicate-update-in-postgresql – Barbaros Özhan Oct 12 '18 at 10:31
  • 1
    `on conflict do nothing` **not** `on conflict` –  Oct 12 '18 at 10:32
  • 1
    Excellent comments thanks, I am using postgres (PostgreSQL) 9.4.17 so I need to upgrade to > 9.5 then I will use ON CONFLICT DO NOTHING – mrmannione Oct 12 '18 at 10:46
  • If you go through the work to upgrade, you should go straight to v10 (or wait 2 weeks an upgrade to v11) –  Oct 12 '18 at 10:50
  • ERROR: duplicate key value violates unique constraint objects when I tried to use "ON CONFLICT DO NOTHING" – mrmannione Oct 12 '18 at 11:45

0 Answers0