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

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