The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/11/functions-comparisons.html
Description:
I recently wanted to do something like "not($col like
all($subquery_that_returns_multiple_values))"
but wasted quite a bit of time trying to debug why it was erroring. Perhaps
the error message or the docs could be clearer?
The error was:
<snip>
ERROR: operator does not exist: text ~~ text[]
LINE 4: and not(t.render_json::text like all(
^
HINT: No operator matches the given name and argument type(s). You might
need to add explicit type casts.
</snip>
which confused me because it seems like the docs say all() takes "something
that returns a pg array" here:
<snip>
expression operator ALL (array expression)
The right-hand side is a parenthesized expression, which must yield an array
value.
</snip>
But I eventually found out that all() takes a pg array or a subquery but not
a subquery that returns a pg array.
To fix my query I had to do remove the array_agg() in the subquery.