Re: pgsql gives ExecutePlan error after empty UPDATE?

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