Re: pgsql gives ExecutePlan error after empty UPDATE?

David C Mudie <mudie(at)digitaldeck(dot)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