title: Postgres - How to achieve UNION behaviour with UNION ALL?
body: <p>I have a table with <code>parent</code> and <code>child</code> ids.</p>
  <pre class="lang-sql prettyprint-override"><code>create table if not exists stack (
      parent int,
      child int
  )
  </code></pre>
  <p>Each parent can have multiple children and each child can have multiple children again.</p>
  <pre class="lang-sql prettyprint-override"><code>insert into stack (parent, child) values
      (1,2),
      (2,3),
      (3,4),
      (4,5),
      (5,6),
      (6,7),
      (7,8),
      (8,9),
      (9,null),
      (1,7),
      (7,8),
      (8,9),
      (9,null);
  </code></pre>
  <p>The data looks like this.</p>
  <pre><code>|parent|child|
  |------|-----|
  |1     |2    |
  |2     |3    |
  |3     |4    |
  |4     |5    |
  |5     |6    |
  |6     |7    |
  |7     |8    |
  |8     |9    |
  |9     |NULL |
  |1     |7    |
  |7     |8    |
  |8     |9    |
  |9     |NULL |
  </code></pre>
  <p>I'd like to find all children. I can use a recursive cte with a <code>UNION ALL</code>.</p>
  <pre class="lang-sql prettyprint-override"><code>with recursive cte as (
  select
      child
  from
      stack
  where
        stack.parent = 1
  union
  select
      stack.child
  from
      cte
  left join stack on
      cte.child = stack.parent
  where
      cte.child is not null
    )
  select * from cte;
  </code></pre>
  <p>This gives me the result I'd like to achieve.</p>
  <pre><code>|child|
  |-----|
  |2    |
  |7    |
  |3    |
  |8    |
  |4    |
  |9    |
  |5    |
  |NULL |
  |6    |
  </code></pre>
  <p>However I'd like to include the depth / level and also the path for each node. I can do this using a different recursive cte.</p>
  <pre class="lang-sql prettyprint-override"><code>with recursive cte as (
  select
      parent,
      child,
      0 as level,
      array[parent,
      child] as path
  from
      stack
  where
        stack.parent = 1
  union all
  select
      stack.parent,
      stack.child,
      cte.level + 1,
      cte.path || stack.child
  from
      cte
  left join stack on
      cte.child = stack.parent
  where
      cte.child is not null
    )
  select * from cte;
  </code></pre>
  <p>That gives me this data.</p>
  <pre><code>|parent|child|level|path                |
  |------|-----|-----|--------------------|
  |1     |2    |0    |{1,2}               |
  |1     |7    |0    |{1,7}               |
  |2     |3    |1    |{1,2,3}             |
  |7     |8    |1    |{1,7,8}             |
  |7     |8    |1    |{1,7,8}             |
  |3     |4    |2    |{1,2,3,4}           |
  |8     |9    |2    |{1,7,8,9}           |
  |8     |9    |2    |{1,7,8,9}           |
  |8     |9    |2    |{1,7,8,9}           |
  |8     |9    |2    |{1,7,8,9}           |
  |4     |5    |3    |{1,2,3,4,5}         |
  |9     |     |3    |{1,7,8,9,}          |
  |9     |     |3    |{1,7,8,9,}          |
  |9     |     |3    |{1,7,8,9,}          |
  |9     |     |3    |{1,7,8,9,}          |
  |9     |     |3    |{1,7,8,9,}          |
  |9     |     |3    |{1,7,8,9,}          |
  |9     |     |3    |{1,7,8,9,}          |
  |9     |     |3    |{1,7,8,9,}          |
  |5     |6    |4    |{1,2,3,4,5,6}       |
  |6     |7    |5    |{1,2,3,4,5,6,7}     |
  |7     |8    |6    |{1,2,3,4,5,6,7,8}   |
  |7     |8    |6    |{1,2,3,4,5,6,7,8}   |
  |8     |9    |7    |{1,2,3,4,5,6,7,8,9} |
  |8     |9    |7    |{1,2,3,4,5,6,7,8,9} |
  |8     |9    |7    |{1,2,3,4,5,6,7,8,9} |
  |8     |9    |7    |{1,2,3,4,5,6,7,8,9} |
  |9     |     |8    |{1,2,3,4,5,6,7,8,9,}|
  |9     |     |8    |{1,2,3,4,5,6,7,8,9,}|
  |9     |     |8    |{1,2,3,4,5,6,7,8,9,}|
  |9     |     |8    |{1,2,3,4,5,6,7,8,9,}|
  |9     |     |8    |{1,2,3,4,5,6,7,8,9,}|
  |9     |     |8    |{1,2,3,4,5,6,7,8,9,}|
  |9     |     |8    |{1,2,3,4,5,6,7,8,9,}|
  |9     |     |8    |{1,2,3,4,5,6,7,8,9,}|
  </code></pre>
  <p>My problem is that I have a lot of duplicate data. I'd like to get the same result as the <code>UNION</code> query but with the level and the path.</p>
  <p>I tried something like</p>
  <pre class="lang-sql prettyprint-override"><code>where
      cte.child is not null
      and stack.parent not in (cte.parent)
  </code></pre>
  <p>or</p>
  <pre class="lang-sql prettyprint-override"><code>where
      cte.child is not null
      and not exists (select parent from cte where cte.parent = stack.parent)
  </code></pre>
  <p>but the first does not change anything and the second returns an error.</p>
  <pre><code>ERROR: recursive reference to query &quot;cte&quot; must not appear within a subquery
  </code></pre>
  <p>Any ideas? Thank you very much!</p>