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?
Add a comment
|
1 Answer
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
WITHquery 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)
answered Dec 15, 2020 at 18:54
-
"Parent query" seems to be
SELECT 42in 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)?– xehpukCommented Dec 15, 2020 at 19:09 -
Yes, exactly. You could use something like
WHERE EXISTS (SELECT 1 FROM division_by_zero). Commented Dec 15, 2020 at 19:12