Thread: BUG #5385: gd disallows agg function in subselect of update

The following bug has been logged online:

Bug reference:      5385
Logged by:          Corin
Email address:      wakathane@gmail.com
PostgreSQL version: 8.4.2
Operating system:   ubuntu amd64
Description:        gd disallows agg function in subselect of update
Details:

query:
UPDATE imagecontest_participations AS g SET rating_ratings_sum=(SELECT
SUM(rating_ratings_sum) FROM imagecontest_participation_ratings WHERE
imagecontest_participation_id=g.id)

error message:
cannot use aggregate function in UPDATE

while looking for a solution I found this commit:
http://archives.postgresql.org/pgsql-committers/2006-06/msg00299.php
... "Disallow aggregate functions in UPDATE commands (unless within a
sub-SELECT).
This is disallowed by the SQL spec because it doesn't have any very
sensible
interpretation." ...

As the subquery is not top-level but inside a subquery I suspect this being
an error. I don't want to use the UPDATE ... FROM syntax if possible to keep
compatibility with other sql servers.

Sorry, stupid me - it's not a bug, pg is perfectly right!

The query was just wrong, because I used the wrong column in the
SUM(...) function.

Sorry again,
Corin

Corin <wakathane@gmail.com> writes:
> Sorry, stupid me - it's not a bug, pg is perfectly right!
> The query was just wrong, because I used the wrong column in the
> SUM(...) function.

Yah.  The reason the error message was phrased that way is that the SUM
argument is a variable of the outer query, so the aggregate is also
considered an aggregate of the outer query.

            regards, tom lane