[BUGS] 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: wakath...@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. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5385: gd disallows agg function in subselect of update
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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5385: gd disallows agg function in subselect of update
Corin 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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
