Re: unexpected update behavior with temp tables

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".