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