0

Consider this CTE:

WITH division_by_zero AS (
  SELECT 1/0
)
SELECT 42

It returns 42 instead of raising an error. How can I force PostgreSQL to evaluate the SELECT 1/0?

1 Answer 1

2

That is because the main query doesn't reference division_by_zero anywhere.

The documentation says:

[...] PostgreSQL's implementation evaluates only as many rows of a WITH query as are actually fetched by the parent query.

That is zero in your case.

If you want the CTE to be executed, you could for example add a WHERE condition like

WHERE EXISTS (SELECT 1 FROM division_by_zero)
2
  • "Parent query" seems to be SELECT 42 in this case (the documentation is missing a definition of the term). I have to somehow reference the "child query" (in my case, it's a function call with side effects)?
    – xehpuk
    Dec 15, 2020 at 19:09
  • Yes, exactly. You could use something like WHERE EXISTS (SELECT 1 FROM division_by_zero). Dec 15, 2020 at 19:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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