Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE post (id INT, parent INT);
INSERT INTO post VALUES
(1, NULL),
(2,5),
(3,4),
(4,NULL),
(5,1),
(6,8),
(7,1),
(8,2),
(9,3);

CREATE TABLE



INSERT 0 9


WITH RECURSIVE
tree (id, root, lvl) AS (
    SELECT id, id root, 1
    FROM post
    WHERE parent IS NULL
UNION ALL
    SELECT post.id, tree.root, tree.lvl + 1
    FROM post
    JOIN tree ON tree.id = post.parent
) 
SELECT *
FROM tree
ORDER BY root, lvl


id
root
lvl


1
1
1

7
1
2

5
1
2

2
1
3

8
1
4

6
1
5

4
4
1

3
4
2

9
4
3



SELECT 9


WITH RECURSIVE
tree (id, root, path, lvl) AS (
    SELECT id, id, id::TEXT, 1
    FROM post
    WHERE parent IS NULL
UNION ALL
    SELECT post.id, tree.root, tree.path || '-' || post.id::TEXt, tree.lvl + 1
    FROM post
    JOIN tree ON tree.id = post.parent
) 
SELECT *
FROM tree
ORDER BY root, path


id
root
path
lvl


1
1
1
1

5
1
1-5
2

2
1
1-5-2
3

8
1
1-5-2-8
4

6
1
1-5-2-8-6
5

7
1
1-7
2

4
4
4
1

3
4
4-3
2

9
4
4-3-9
3



SELECT 9

id	root	path	lvl
1	1	1	1
5	1	1-5	2
2	1	1-5-2	3
8	1	1-5-2-8	4
6	1	1-5-2-8-6	5
7	1	1-7	2
4	4	4	1
3	4	4-3	2
9	4	4-3-9	3