Re: pgsql gives ExecutePlan error after empty UPDATE? - Mailing list pgsql-general

From Tom Lane
Subject Re: pgsql gives ExecutePlan error after empty UPDATE?
Date
Msg-id 23918.963423724@sss.pgh.pa.us
Whole thread Raw
In response to pgsql gives ExecutePlan error after empty UPDATE?  (David C Mudie <mudie@digitaldeck.com>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Jeff Waugh
Date:
Subject: Re: Re: [INTERFACES] Re: Link to postgesql components
Next
From: root
Date:
Subject: Re: Re: [NOVICE] newbie problem on creating table