您将需要递归来完成此任务：

with recursive hier as (
  select *, 0 as hlevel
    from idparent
   where parent_id is null
  union all
  select c.*, p.hlevel + 1
    from hier p
    join idparent c on p.id = c.parent_id
)
select * from hier
 order by hlevel;

 id | parent_id | hlevel 
----+-----------+--------
  2 |           |      0
  3 |         2 |      1
  4 |         2 |      1
  1 |         3 |      2
(4 rows)

我认为最安全的方法是使用递归的cte来计算每个路径 id . 那就分类：

with recursive cte as (
      select id, parent_id,  array[id] as path
      from t
      where parent_id is null
      union all
      select t.id, t.parent_id, path || array[t.id]
      from cte join
           t
           on cte.id = t.parent_id
     )
select *
from cte
order by path;

这是一把小提琴。