Conditional insert vs UNIQUE constraint 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?
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.