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

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Peter V <peterv861908(at)hotmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: WITH x AS (...) and visibility in UPDATE
Date: 2011-07-27 21:16:48
Message-ID: CAHyXU0yyzePu8PJx_fgZU=rZMY93nshpNWiMKG+rvpDZFaLPVA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Wed, Jul 27, 2011 at 4:03 PM, Peter V <peterv861908(at)hotmail(dot)com> wrote:
>> On Wed, Jul 27, 2011 at 3:18 PM, Peter V <peterv861908(at)hotmail(dot)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
>
>
>
> 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 ;)

not in the exact sense you were trying. what is it you are trying to
do in general? I'm having trouble understanding your use-case.

merlin

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter V 2011-07-27 21:22:08 Re: WITH x AS (...) and visibility in UPDATE
Previous Message Peter V 2011-07-27 21:03:43 Re: WITH x AS (...) and visibility in UPDATE