David C Mudie <mudie@digitaldeck.com> writes:
> zorro_4330=# update foo set bar = count(*) from foo \g
> ERROR: ExecutePlan: (junk) `ctid' is NULL!
> Postgres seems to be reporting an error because the update matched no rows,
> but this a perfectly legitimate database operation.
Actually, it's flat-out illegal according to SQL92: thou shalt not
use an aggregate in UPDATE, quoth the standard. Try it with the
count() in a sub-select, which is legal SQL:
update foo set bar = (select count(*) from foo);
There is a thread going on in pghackers right now about whether it
makes sense to allow aggregates outside sub-selects in UPDATE, and
if so what it should mean exactly.
regards, tom lane