Re: unexpected update behavior with temp tables

From: Timothy Perrigo <tperrigo(at)wernervas(dot)com>
To: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: unexpected update behavior with temp tables
Date: 2004-07-08 14:28:16
Message-ID: 0D47F9CD-D0EB-11D8-A4DE-000A95C4F0A2@wernervas.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


On Jul 8, 2004, at 9:14 AM, Stephan Szabo wrote:

>
> On Thu, 8 Jul 2004, Timothy Perrigo wrote:
>
>> OPT=# select id as not_id, b into temp temp_foo from foo where b =
>> 'Tim';
>> SELECT
>> OPT=# select * from temp_foo;
>> not_id | b
>> --------+-----
>> 1 | Tim
>> (1 row)
>>
>> OPT=# update foo set b = 'Timothy' where id in (select id from
>> temp_foo);
>
> Subselects like that are AFAIK allowed to see outer columns according
> to
> the SQL spec. Thus, the id inside the subselect is effectively foo.id.
>
> This behavior is useful when you want to do something like a function
> or
> operator on an inner column and an outer column inside the subselect
> and
> painful in cases like this where effectively the clause becomes "id is
> not
> null" which for a primary key is itself a long way of saying "true".
>
>
Thanks for the reply, Stephan. I guess I can see the rationale for
this, though it is quite easy to cause yourself quite a bit of grief.
It would certainly make things safer if columns in the subselect which
refer to columns in the table from the outer query where required to be
fully specified (i.e. "foo.id", instead of just "id"), but if this
behavior is part of the standard, I imagine there's little chance of
changing it...

I appreciate the assistance!

Tim

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message gearond 2004-07-08 16:19:36 Re: Column name 'user' not allowed?
Previous Message Stephan Szabo 2004-07-08 14:14:44 Re: unexpected update behavior with temp tables