By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t(id INT, parent_id INT)
INSERT INTO t(id, parent_id) VALUES (1, NULL),(2,1),(3,2), (1,3)
Records: 4 Duplicates: 0 Warnings: 0
SELECT * FROM t
| id | parent_id |
|---|---|
| 1 | null |
| 2 | 1 |
| 3 | 2 |
| 1 | 3 |
WITH RECURSIVE cte AS (
SELECT id, parent_id, 0 lvl
FROM t WHERE parent_id IS NULL
UNION ALL
SELECT t.id, t.parent_id, lvl + 1 AS lvl
FROM cte c1
JOIN t ON c1.id = t.parent_id
)
CYCLE id, parent_id RESTRICT
SELECT * FROM cte ORDER BY lvl;
| id | parent_id | lvl |
|---|---|---|
| 1 | null | 0 |
| 2 | 1 | 1 |
| 3 | 2 | 2 |
| 1 | 3 | 3 |