Get the latest tech news
How to Get or Create in PostgreSQL
And why it is so easy to get wrong...
Attempting to insert a tag and handle a potential unique constraint violation is idempotent and safe when executed by concurrent processes, but there is a hidden downside. This article by the Amazon RDS team explains the issue pretty well - in an INSERT command, the row is first added to the table (heap), and only then checked for potential unique constraint violations. In processes where duplicates rarely happen, it's perfectly fine to rely on catching unique constraint violations to implement "get or create" functionality.
Or read this on Hacker News
