From: | Timothy Perrigo <tperrigo(at)wernervas(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | unexpected update behavior with temp tables |
Date: | 2004-07-08 13:16:32 |
Message-ID: | 078F7FF3-D0E1-11D8-A4DE-000A95C4F0A2@wernervas.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | btober | 2004-07-08 13:26:55 | Re: SUBSTRING for a regular expression |
Previous Message | Marco Colombo | 2004-07-08 11:06:17 | Re: Enough RAM for entire Database.. cost aside, is thi |