Re: unexpected update behavior with temp tables - Mailing list pgsql-general

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);
> UPDATE 4
> OPT=# select * from foo;
>  id |    b
> ----+---------
>   1 | Timothy
>   2 | Timothy
>   3 | Timothy
>   4 | Timothy
> (4 rows)

I think I can see what's happening, but don't know enough internals to
say why.

The "id" in the subselect must be binding to the outer query. I could
see how that might be desirable in some circumstances, but could easily
cause trouble in many cases.

--
   Richard Huxton
   Archonet Ltd