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

From: Peter V <peterv861908(at)hotmail(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: WITH x AS (...) and visibility in UPDATE
Date: 2011-07-27 20:18:06
Message-ID: BAY149-W55A7FB9E6B52EF32783636C1350@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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?

Thanks.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2011-07-27 20:58:04 Re: WITH x AS (...) and visibility in UPDATE
Previous Message John Cheng 2011-07-27 19:56:09 Re: Cannot reproduce why a query is slow