How to save an order (permutation) in sql db

Question

I have a tree structure in a sql table, for example:

CREATE TABLE containers (
 container_id serial NOT NULL PRIMARY KEY,
 parent integer REFERENCES containers (container_id))

Now I want to determine the order between nodes with the same parent.
I thought about adding a column node_indexto ORDER BY, but this seems to be suboptimal, since this is due to the modification of the index of a large number of nodes when the structure changes.
This may include adding, removing, reordering, or moving nodes from one subtree to another.

Is there an sql data type for ordered sequence or an efficient way to simulate? No need to be fully standard sql, I just need a solution for mssqland hopefullypostgresql

EDITTo be clear, the order is arbitrary. In fact, the user will be able to drag the tree nodes in the graphical interface

Answer 1

Using the idea of ​​node_index, you can easily update values ​​using simple SQL (you can increase / decrease the value in an UPDATE statement). To do better, I think we will need to learn more specific things about what you store and how you modify it.

Answer 2

Assuming you don’t want to place an order for one of your existing values, I’m afraid that you will need another column to store some sort of index column. sql does not have the concept of an ordered sequence as such.