# Determine existence in recursive working table

I have a query that needs to check for existence of each input row type in another table, but it is unnecessary and inefficient to check for existence of subsequent rows of the same row type if one is already found.

I tried to reference the working table in an `EXISTS` subquery in the `JOIN` to prevent the unnecessary checks, but this gave an error that the recursive query cannot be referenced in a subquery.

For a simplified example modified from the docs only meant to demonstrate my problem in code:

``````WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100 AND NOT EXISTS (SELECT 1 FROM t u WHERE u.n = t.n+1)
)
SELECT sum(n) FROM t;
``````

This is of course logically ridiculous, but the `NOT EXISTS` is the problem and specifically what gives an error.

Is there a way to determine existence in the working table?

• Just curious if you have read the documentation on how recursion works in Postgres? (in regard to the working table vs result set..) postgresql.org/docs/9.3/static/queries-with.html Jan 27, 2015 at 19:20
• @JoishiBodio I have. Is there something obvious I've missed that you're trying to show me? The input rows are not duplicates, if that's what you mean. Jan 27, 2015 at 19:26
• Just that usually, in terms of recursion, if something is "logically ridiculous", then the recursion is probably not right.. You could look at your problem (once you fix the syntax), manually track the working table and result set, and see where the logic might be wrong. I will see about submitting an answer that will correct your syntax error.. Jan 27, 2015 at 19:34
• @JoishiBodio The ridiculousness is the overall example I provided. I provided it to show in code which exact part fails. In the case of my query, somehow checking for existence in the working table is logical, necessary, and efficient. I would be very grateful if you could show me how to determine existence in the working table. Jan 27, 2015 at 19:37
• You don't need to determine existence in the working table .. those are added to the result set .. so you can just determine existence in the result set. If, for some reason, you need to determine existence in the result set AS WELL AS DEPTH OF RECURSION ... then you usually also add a counter (like n in your example) to track the recursion count. Then you can also check the recursion count to try to "narrow the result set" to just the working table. Jan 27, 2015 at 19:40

I'm not sure I fully understand what you expect to happen with the SQL you pasted .. (add up the numbers from 1-99 ignoring all even values?) But this is a SQL to get around the syntax error you have:

``````WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n+1 not in (t.n) and n < 100
)
SELECT sum(n) FROM t;
``````

I would like to point out that the result of my SQL above returns 5050 because the "n+1" is never actually in the result set... (You'd need to compare for n-1 in the result set if you wanted to ignore even numbers..)

• Wonderful! I had taken `EXISTS` so to heart because of its efficiency that I completely ignored `IN`. Please give me some time to verify for points. Jan 27, 2015 at 19:41
• No prob. I hope it solves your issue. :) Jan 27, 2015 at 19:42