PostgreSQL select child_id as parent_id

Question

I have a graph structure of nodes that looks like this

                      1
            |---------|---------|
            2                   3
      |-----|-----|       |-----|-----|
      4           5       6           7

I store the relationships of the structure in a SQL database as

====== node table =======
id | parent_id | child_id
-------------------------
1  |     1     |     2
2  |     1     |     3
3  |     2     |     4
4  |     2     |     5
5  |     3     |     6
6  |     3     |     7

I want to query these relationships as

====== node table =======
parent_id | child_id
-------------------------
    1     |     2
    1     |     3
    2     |     4
    2     |     5
    3     |     6
    3     |     7
    4     |    null
    5     |    null
    6     |    null
    7     |    null

Currently, I'm trying to use UNION on the child part to concatenate records.

select
    t.parent_id ,
    t.child_id 
from
    table t
union
select 
    t2.child_id ,
    null,
    null
from
    table t2
where
    t22.child_id not in (
    select
        distinct t3.parent_id
    from
        table t3)

I'm wondering if there's a better way to achieve this ?

Answer 1

This is quite simple:

SELECT A.parent_id, B.child_id 
FROM (
  SELECT parent_id FROM test
  UNION 
  SELECT child_id FROM test
) AS A
LEFT JOIN test AS B ON A.parent_id = B.parent_id

First you get all the graph node IDs (both parents and children) - then you try to find all children for each parent.