Unique partial indexes with PostgreSQL
How to use unique partial indexes to avoid conditional duplication
When you need to deal with concurrency and unique records databases are your friend. Unique indexes are the best way to avoid data inconsistence and duplication.