sqlpostgresqlhierarchyhierarchical

postgresql hierarchy table based on two column


I need to make sure we have support to create a hierarchy table based on a "father/son" table. Example:

F S
a b
b c
b d
b e
e f
e g
b m
z n
m k

Expected result:

L L1 L2 L3
a b c
a b d
a b e
e f
e g
a b m k
z n

Any idea, suggestion?

Thank you


Solution

  • with 
    recursive cte(id, path) as
    (
    select f, f::text from   t where  f not in(select s from t where s is not null)
    union all
    select t.s, cte.path || '/' || t.s from cte join t on cte.id = t.f 
    )
    
    select  id      
           ,path[1] as root
           ,path[2] as l1
           ,path[3] as l2
           ,path[4] as l3
           ,path[5] as l4
    from 
    (
    select  id 
           ,regexp_split_to_array(path, '/') as path
    from    cte
    ) cte
    
    id root l1 l2 l3 l4
    a a null null null null
    z z null null null null
    b a b null null null
    n z n null null null
    m a b m null null
    e a b e null null
    d a b d null null
    c a b c null null
    k a b m k null
    g a b e g null
    f a b e f null

    Fiddle