1

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 – Joishi Bodio Jan 27 '15 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. – Jim Bob Jan 27 '15 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.. – Joishi Bodio Jan 27 '15 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. – Jim Bob Jan 27 '15 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. – Joishi Bodio Jan 27 '15 at 19:40
2

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. – Jim Bob Jan 27 '15 at 19:41
  • No prob. I hope it solves your issue. :) – Joishi Bodio Jan 27 '15 at 19:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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