4 ms·

As far as I remember it isn’t that simple. Moving operations now require extra queries and reshuffling of order. Doable, just far less elegant for a very common

• maxbond 3y ago
  My suggestion would be to write your tree using an approach that is friendly to whatever constraints you need to impose or whatever shuffling operations you'll need to perform, and then use a view or computed column to materialize your paths as an ltree. Then you can use the ltree and accompanying index type for efficient path queries, but you still have the power to manage the sorts of details you mention when you need them. Put another way, ltrees are a denormalized representation; we denormalize to optimize lookups. Other approaches that use foreign keys and such are more normalized representations; we normalize to give ourselves the power to enforce constraints and to optimize update operations. But as long as we can trade some storage and write amplification for performance on lookups, we aren't tied to one approach or the other, we can mix and match.