This bug? feature? caused a bit of havoc for us yesterday...A
reproducible example follows. Essentially, if you have a table with a
primary key called "id", and you create a temp table (via a "select
into") containing a subset of the data from the table but where the
primary key field is renamed (in the example below, it is called
"not_id"), the where clause of the following update statement (which I
would expect to generate an error saying that the temp table has no
column named "id") matches _all_ the rows in your table, updating them
all! Why does this statement work? Shouldn't it result in an error?
OPT=# create table foo (id serial, b varchar, constraint foo_pkey
primary key(id));
NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq" for
"serial" column "foo.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
OPT=# insert into foo (b) values ('Tim');
INSERT 1178158 1
OPT=# insert into foo (b) values ('Ben');
INSERT 1178159 1
OPT=# insert into foo (b) values ('Erin');
INSERT 1178160 1
OPT=# insert into foo (b) values ('Bob');
INSERT 1178161 1
OPT=# select * from foo;
id | b
----+------
1 | Tim
2 | Ben
3 | Erin
4 | Bob
(4 rows)
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)
The following update, which attempt to use a non-existent column named
"bogus", demonstrates the behavior I would expect to see:
OPT=# update foo set b = 'Sam' where id in (select bogus from temp_foo);
ERROR: column "bogus" does not exist
