Postgres: Transaction not honouring previous queries
Hi, I have a table with unique constraint on some columns.
In a transaction, I am deleting all the entries from the table and then I am inserting new entries. However, I am getting
error: duplicate key value violates unique constraint "xxxxx_key"
I only want the deletion to happen if the insertion is successful, and that's why I tried using transaction. However, the transaction seems to work in a different way than I thought.
Can someone help me in understanding this behaviour and what's the solution to this?Solution:Jump to solution
If you insert before you delete, that's what's going to happen. You insert something and only after that delete the duplicates. The constraint is checked immediately when you insert.
4 Replies
Can you share the code you're running?
Solution
If you insert before you delete, that's what's going to happen. You insert something and only after that delete the duplicates. The constraint is checked immediately when you insert.
You can create deferred unique constraints that are only checked after the transaction.
I was doing the deletion before inserting the data. However, I was making a bulk insert and the array had duplicate data.
Nothing to do with transaction.