4 ms·

This works well until you need to preserve children order then it doesn’t quite work without further mods

• michaelsalim 3y ago
  Then add a sort column just like any other implementation. Even the example given by the article uses it.
  • qudat 3y ago
    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 use case.
    • 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.
• tejtm 3y ago
  Yes. In case you ever work with an Anatomist, and they are visibly freaking out at you destroying everything it is because they order parts anterior to posterior and it is sooooooo obvious they wont ever feel the need to mention it ...