Perfect. I had tried the `= ANY` construct but got the same syntax error and mistakenly assumed it was a problem with the parser rather than my misreading of the `ANY (array)` docs (9.24.3).
Eric Atkin <eatkin@certusllc.us> writes: > 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.
Correct, but the problem is not with the array_agg call, it's with your use of IN. In the first place, IN requires parens around its righthand side. But that only gets us past "syntax error":
regression=# SELECT city, (SELECT count(*) FROM delivery WHERE driver_id IN (array_agg(driver.id))) AS deliveries FROM driver GROUP BY city ; ERROR: operator does not exist: integer = integer[] LINE 3: ... (SELECT count(*) FROM delivery WHERE driver_id IN (array_... ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
That's because what's inside the parens is supposed to be either a sub-select or a list of things directly comparable to the LHS. To do what you're after, you need to use the "scalar = ANY(array)" construct:
regression=# SELECT city, (SELECT count(*) FROM delivery WHERE driver_id = any (array_agg(driver.id))) AS deliveries FROM driver GROUP BY city ; city | deliveries ------+------------ (0 rows)
IN and =ANY are more-or-less equivalent when the RHS is a sub-select, but not for an array RHS.
There's nothing particularly wrong with Laurenz's construction, but it's not necessary to split it up like that.
