any() and ilk wrt to what they accept

From: PG Doc comments form <noreply(at)postgresql(dot)org>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Cc: justin(at)devuyst(dot)com
Subject: any() and ilk wrt to what they accept
Date: 2019-08-08 15:01:47
Message-ID: 156527650771.8639.12036785429169652776@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

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.

Browse pgsql-docs by date

  From Date Subject
Next Message PG Doc comments form 2019-08-12 08:17:05 Better Example
Previous Message Joshua D. Drake 2019-08-07 22:52:05 Update and modernization to intro.sgml