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

From: Peter V
Subject: Re: WITH x AS (...) and visibility in UPDATE
Date: ,
Msg-id: BAY149-W11FF400DCCF9663DE57D60C1350@phx.gbl
(view: Whole thread, Raw)
In response to: Re: WITH x AS (...) and visibility in UPDATE  (Merlin Moncure)
Responses: Re: WITH x AS (...) and visibility in UPDATE  (Merlin Moncure)
List: pgsql-general

Tree view

WITH x AS (...) and visibility in UPDATE  (Peter V, )
 Re: WITH x AS (...) and visibility in UPDATE  (Merlin Moncure, )
  Re: WITH x AS (...) and visibility in UPDATE  (Peter V, )
   Re: WITH x AS (...) and visibility in UPDATE  (Merlin Moncure, )
    Re: WITH x AS (...) and visibility in UPDATE  (Peter V, )
     Re: WITH x AS (...) and visibility in UPDATE  (Jack Christensen, )
     Re: WITH x AS (...) and visibility in UPDATE  (David Johnston, )



----------------------------------------
> Date: Wed, 27 Jul 2011 15:58:04 -0500
> Subject: Re: [GENERAL] WITH x AS (...) and visibility in UPDATE
> From: 
> To: 
> CC: 
>
> On Wed, Jul 27, 2011 at 3:18 PM, Peter V <> 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



This makes sense. I thought that the insert was evaluated first, before the join is resolved. This isn't the case
apparently.



Is there another way to force this? That is, without extracting it to two statements ;)



Thanks.



pgsql-general by date:

From: sqldbxdeveloper
Date:
Subject: Re: problems viewing information_schema.schemata
From: David Johnston
Date:
Subject: Re: WITH x AS (...) and visibility in UPDATE