Resources on modeling ordered hierachies?

From: Tim Uckun <timuckun(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Resources on modeling ordered hierachies?
Date: 2022-04-07 05:45:49
Message-ID: CAGuHJrM9xwbr9YfJYw21GW6TJc4Jqyq-BUXL4qF1HS0J-gT1fg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

There a tons of articles about how to model hierarchies in SQL but I
haven't seen any about dealing with hierarchies where the order of
children is important.

The canonical example is a simple outline

1.
1.1
1.1.1
1.2
2.
2.1

etc

If I am doing an insert where parent is 1.1 it should name it 1.1.2
which to me means doing something like select max(id) + 1 where parent
= 1.1 or something like that which might turn out to be expensive.

Similarly if I want to insert something between 1.1 and 1.2 I need to
do something like update id set id = id+1 where parent = 1 and id >1

Items could of course be "promoted" this way or moved up and down the
hierarchy to be below or above their peers.

Does anybody know of a more elegant way to do this?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message W.P. 2022-04-07 06:23:08 Re: Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)
Previous Message Perry Smith 2022-04-06 23:15:41 Re: What have I done!?!?!? :-)