0

https://stackoverflow.com/questions/4069718/postgres-insert-if-does-not-exist-already

I would like to check if something exists before inserting it, what should I rely on?

UNIQUE constraint or INSERT .. INTO .. WHERE NOT EXISTS .. or maybe both? Is there any other solution?

Is it possible to check if something exists in a separate query and inserting in a separate query in the way that it's atomic? Putting it in a transaction scope maybe?

Improve this question
9
  • 2
    Both. Unique constraint guarantees duplicates absence ever when errorneous query executed, and WHERE NOT EXISTS prevents duplication errors during insert execution.
    – Akina
    Feb 21, 2019 at 12:36
  • 2
    What's wrong with insert ... on conflict do nothing and a unique constraint?
    – user1822
    Feb 21, 2019 at 13:51
  • 4
    It is faster and you don't need to deal with errors.
    – user1822
    Feb 21, 2019 at 13:53
  • 2
    It also checks all UNIQUE (and EXCLUDE) constraints. With WHERE NOT EXISTS, you'd have to add all the conditions (one for each constraint) and modify the INSERT query when the schema (constraints) change
    – ypercubeᵀᴹ
    Feb 21, 2019 at 13:56
  • 1
    @Konrad less complexity on the one hand, and all the pros ypercube and a_horse_with_no_name listed.
    – dezso
    Feb 21, 2019 at 18:04

1 Answer 1

Reset to default
1

It depends on what you are needing from the solution. If you want to 100% prevent duplicate errors you go with a constraint. If it is to minimize duplicate rows and prevent errors from happening you go with the conditional insert. If you need to prevent duplicate rows and the errors from attempting to insert a duplicate row you go with both.

If you are depending on the conditional insert you can get a duplicate row if someone is doing an insert and they do not add the condition as expected.

If you just use the constraint you will get errors that could be prevented (sometimes you want to know of the error) when attempting to insert the duplicate record.

Improve this answer
2
  • will preventing the constraint error make any difference in performance?
    – Konrad
    Feb 21, 2019 at 13:47
  • @Konrad I am not sure on that but I would think it would depend on the constraint and what all needs to be checked. As for preventing the error I work with the goal of preventing them unless there is a reason you need to know of them happening.
    – Joe W
    Feb 21, 2019 at 13:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.