Postgres SQL Check for duplicate records before inserting

Asked Oct 12 '18 at 10:10

Active Oct 12 '18 at 10:14

Viewed 264 times

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.

Perform INSERT then if there is a duplicate key exception, I handle that.
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.

edited Oct 12 '18 at 10:14

a_horse_with_no_name

497,550
91
775
843

asked Oct 12 '18 at 10:10

mrmannione

3

Why not simply use `insert on conflict`? – a_horse_with_no_name 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 – a_horse_with_no_name 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` – a_horse_with_no_name 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) – a_horse_with_no_name 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

Postgres SQL Check for duplicate records before inserting

0 Answers0