"The aggregate expression as a whole is then an outer reference
for the subquery it appears in, and acts as a constant over any one
evaluation of that subquery."
It seems the aggregate function call should be evaluated at the outer layer (where it would not be bad syntax) and then is a constant for the inner query where clause where an array would be allowed.
Thank you for your working example. There is often more than one way to write any particular query. My version and yours seem to mean the same thing, but one is arguably more readable. At the very least, is this not a documentation bug?
On Fri, 2024-04-12 at 17:14 +0000, PG Bug reporting form wrote:
> SELECT
> city,
> (SELECT count(*) FROM delivery WHERE driver_id IN array_agg(driver.id))
> AS deliveries
> FROM driver
> GROUP BY city
> ;
>
> This produces:
>
> ERROR: syntax error at or near "array_agg"
> LINE 3: ...(SELECT count(*) FROM delivery WHERE driver_id IN array_agg(...
This not a bug, but bad syntax.
Write
SELECT city,
(SELECT count(*)
FROM delivery
WHERE driver_id = ANY (drivers)) AS deliveries
FROM (SELECT city,
array_agg(driver.id) AS drivers
FROM driver
GROUP BY city) AS q;
Yours,
Laurenz Albe