Re: WITH x AS (...) and visibility in UPDATE

On Wed, Jul 27, 2011 at 3:18 PM, Peter V <peterv861908@hotmail.com> wrote:
>
> Hello all,
>
> I am trying out PostgreSQL 9.1 Beta 3. In particular, I am very interested in WITH x AS (...) construction.
>
> drop table if exists t;
> create table t
> (
>     identifier   serial,
>     title        text
> );
>
> with c as
> (
>     insert into t (title) values ('old') returning *
> )
> update t set title = 'new' from c where t.identifier = c.identifier;
>
> select * from t;
>
> Can someone explain why this returns 'old' instead of 'new'? Is the new row not yet visible when the update is
evaluated?

because the update statement isn't doing anything.  (you could have
confirmed this by adding 'returning *' to the update.

While the 'from c' is working, you can't join back to t yet because
the statement hasn't resolved. here's a reduced form of your problem:

postgres=# with c as
(
    insert into t (title) values ('old') returning *
) select * from t join c using (identifier);

The join fails because at the time it happens t isn't yet populated.

merlin