user2311028 user2311028 - 17 days ago 5
SQL Question

Hierarchical structure in PostgreSQL table

I have a PostgreSQL table that contains the following structure:

Parent child1 child2
1 10 12
2 13
3


I want to have:

Parent child1 child2
1 10 12
2 13 13
3 3 3


I mean, if child2 is NULL, I want to duplicate child1 into child2;
and if child1 is null, I want to duplicate the parent into child1 and child2.

Answer

Do you mean something like:

select Parent,
       coalesce(child1, Parent) as child1,
       coalesce(child2, child1, Parent) as child2
from <tablename>;

?