10

This is tested with PostgreSQL 9.6, but it's a general SQL question.

Is it possible to use self-joins of the recursive table in a recursive CTE (rCTE)?

I tried the following rCTE containing a self-join of the recursive term x,

WITH RECURSIVE
x (id) AS (
  SELECT 1 id UNION ALL SELECT x1.id+x2.id FROM x x1, x x2
  WHERE x1.id < 5 AND x2.id < 5 AND x1.id = x2.id
  )
SELECT * FROM x;

, which hopefully should be equivalent to:

WITH RECURSIVE
x (id) AS (
  SELECT 1 id UNION ALL SELECT id+id FROM x WHERE id < 5
  )
SELECT * FROM x;

But the former rCTE generates an error:

ERROR:  recursive reference to query "x" must not appear more than once 
LINE 3:   SELECT 1 id UNION ALL SELECT x1.id+x2.id FROM x x1, x x2

Is there a fundamental reason why the recursive reference must not appear more than once? Or is this just a limitation of the PostgreSQL implementation?

Also, is there a work-around?

Improve this question
1

2 Answers 2

Reset to default
13

Indeed error like recursive reference to query "x" must not appear more than once is some strange restriction applied in postgres. And I made assumption it is because their parser just simple distinguish recursive and non-recursive part of query by present of that table. Meantime for that present nice workaround - you may use nested CTE (WITH statement), and give another name for such table. For your initial example it will look like:

WITH RECURSIVE
x (id) AS (
  SELECT 1 id
  UNION ALL
  SELECT * FROM (
    WITH x_inner AS ( -- Workaround of error: recursive reference to query "x" must not appear more than once
      SELECT * FROM x
    )
    SELECT x1.id+x2.id
    FROM x_inner x1, x_inner x2
    WHERE x1.id < 5 AND x2.id < 5 AND x1.id = x2.id
  )t
)
SELECT * FROM x;

You could try it in SQL fiddle.

Improve this answer
3
  • 2
    This is voodoo.
    – ADJenks
    Commented Jan 26, 2021 at 19:59
  • 1
    This is a smart solution by taking the error literally: "must not appear more than once". Nice!
    – Christiaan Westerbeek
    Commented Aug 23, 2021 at 8:48
  • 1
    confirmed still needed and still works for postgres PostgreSQL 12.15; many thanks @Hubbitus
    – spioter
    Commented Jul 9, 2023 at 20:15
1

To your first question: by joining x to x you are creating two separate instances of the object. When Postgres tries to recurse it does not know which path (instance) to follow.

Essentially your second query is the work around. Though I assume you have a more complicated situation that this is a simplification of?

Improve this answer
4
  • yes, indeed, the second rCTE is the trivial/degenerate self-join case which cannot replace general self-joins I'd like to explore.
    – tinlyx
    Commented Sep 5, 2017 at 23:32
  • In order to look at how to make the more complicated version possible we'll need to see what that looks like. Otherwise the simpler version is already optimized to its simplest/functional form.
    – indiri
    Commented Sep 5, 2017 at 23:37
  • 2
    also, this is an interesting exercise in what you can and cannot do with recursion: sourceforge.net/p/postgres-xl/postgres-xl/ci/…
    – indiri
    Commented Sep 5, 2017 at 23:56
  • this is a great explanation but at this point it should be made a comment to the actual solution: dba.stackexchange.com/a/240309/210251
    – spioter
    Commented Jul 9, 2023 at 20:16

Not the answer you're looking for? Browse other questions tagged or ask your own question.