unexpected update behavior with temp tables
От | Timothy Perrigo |
---|---|
Тема | unexpected update behavior with temp tables |
Дата | |
Msg-id | 078F7FF3-D0E1-11D8-A4DE-000A95C4F0A2@wernervas.com обсуждение исходный текст |
Ответы |
Re: unexpected update behavior with temp tables
Re: unexpected update behavior with temp tables |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: