Postgres unique index conflict on insert

Question

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.

This seems like a flawed design. If this is a tree, then I should be able to add a new node without renumbering all of the other nodes. — Tim Roberts
– Tim Roberts, Commented Jul 30, 2022 at 1:09
Post your table description (ddl script) and example data - all as formatted text or a fiddle - no images. Also, why do you think you need to increase existing values to insert a node? — Belayer
– Belayer, Commented Jul 30, 2022 at 2:21
I guess the order of your updates is not compatible with this unique index. If you want to stay with the nested set structure and the unique constraint, then you need to reorder them. I think starting at the end might be useful here and then traversing the boundaries towards the start (so it is like a backwards infix traversal, I guess). But as others already said, perhaps rethink if this structure is appropriate. It is surely not if you have much manipulation of data (INSERT, UPDATE or DELETE). — Islingre
– Islingre, Commented Jul 30, 2022 at 2:37
You might also defer the checking of your UNIQUE constraint, see postgresql.org/docs/current/sql-set-constraints.html — Islingre
– Islingre, Commented Jul 30, 2022 at 2:40
This is why I don’t want to use it, and why a CTE is a simpler solution: en.wikipedia.org/wiki/Nested_set_model — Frank Heikens
– Frank Heikens, Commented Jul 30, 2022 at 3:53

Islingre · Accepted Answer · 2022-07-30 02:57:13Z

0

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

answered Jul 30, 2022 at 2:57

Islingre

2,37810 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres unique index conflict on insert

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related