CODEWITHSUNDEEP
postgresqlnested-sets
Dercni
Dercni

Reputation: 1224

Postgres unique index conflict on insert

I have a table with a nested set so each row has a lft & rgt value. When a new row is inserted into the table the nodes following need to have their lft & rgt values incremented by 2 and then the new node can be inserted.

I have an index on both the lft & rgt columns however as these values are unique I wanted to use a unique index.

When inserting a new node the unique index fails advising that the lft value violates the index as it appears the index is checked before the update statement completes. So when a value is incremented by 2 it conflicts before the others can also be incremented by 2.

Is there a way to ignore the index until after the update statement has been completed?

Edit1

For example if I added a child node under "Languages" then its lft & rgt values would be: 4 & 5 and therefore the following nodes would need to be incremented by 2 to make room for the new node.

enter image description here

Upvotes: 0

Views: 694

Answers (1)

Islingre
Islingre

Reputation: 2349

First: Check if your model is appropriate! Nested sets are inappropriate if you have much manipulation of data (INSERT, UPDATE or DELETE).

Second: If you want to stay with nested sets, you might defer the validation of the UNIQUE constraint. The documentation for this can be found at: https://www.postgresql.org/docs/current/sql-set-constraints.html

Upvotes: 0

Related Questions