How to save an order (permutation) in an sql db

Question

I have a tree structure in an sql table like so:

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

Now i want to define an ordering between nodes with the same parent.
I Have thought of adding a node_index column, to ORDER BY, but that seem suboptimal, since that involves modifying the index of a lot of nodes when modifying the stucture.
That could include adding, removing, reordering or moving nodes from some subtree to another.

Is there a sql datatype for an ordered sequence, or an efficient way to emulate one? Doesn't need to be fully standard sql, I just need a solution for mssql and hopefully postgresql

EDIT To make it clear, the ordering is arbitrary. Actually, the user will be able to drag'n'drop tree nodes in the GUI

Answer 1

assuming you don't want to order by one of your existing values, i'm afraid you'll need another column to store some kind of index column. sql has no concept of an ordered sequence per se.

Answer 2

Using the node_index idea you can update the values pretty easily with some simple SQL (you can increment/decrement a value in an UPDATE statement). In order to do better than that I think we'd need to know more specific things about what you are storing and how you would be modifying it.