On Wed, Jul 27, 2011 at 3:18 PM, Peter V <peterv861...@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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
