Consider the following table:

I want the result as such:

1: [2,3,4]
2: [3,4]
3: [4]
4: null

(i.e) Since 1 is the parent of 2 which is the parent of 3 and so on...

I tried this query but it requires a value:

    WITH RECURSIVE a AS (
    SELECT 1 AS id # I need to get all values instead of a specific value
    UNION ALL
    SELECT b."ID"
    FROM "tblName" "b" JOIN "c" ON "c"."id" = "b"."ParentID"
    )
    SELECT * FROM c

CodePudding user response：

Get all rows, recurse downward while keeping track of the level, then use array_agg() to consolidate and array_remove to remove the row's "self" id.

with recursive walk as (
  select id, parentid, id as start_id, 0 as level
    from tblname
  union all
  select c.id, c.parentid, p.start_id, p.level   1 as level
    from walk p
         join tblname c on c.parentid = p.id
)
select start_id, array_remove(array_agg(id order by level), start_id) as children 
  from walk
 group by start_id; 

The nullif() is there only to match your spec.

db<>fiddle here