The following bug has been logged on the website:
Bug reference: 7662
Logged by: Melese Tesfaye
Email address: mtesf...@gmail.com
PostgreSQL version: 9.2.1
Operating system: FreeBSD 9.0-RELEASE
Description:
ON INSERT RULE doesn't always work as expected.
The reasoning for this particular rule was to add the new value to an
existing value if it is determined that the key already exists.
Here is the table definition:
CREATE TABLE test_r(id INT PRIMARY KEY,val INT NOT NULL);
Here is the rule definition:
CREATE OR REPLACE RULE "dup_add_test_r_rule" AS
ON INSERT TO test_r
WHERE
EXISTS(SELECT 1 FROM test_r a WHERE a.id=NEW.id)
DO INSTEAD
(UPDATE test_r a SET val=a.val+NEW.val WHERE a.id=NEW.id);
Empty table:
SELECT * FROM test_r;
+----+-----+
| id | val |
+----+-----+
+----+-----+
(0 rows)
Time: 0.775 ms
Now, insert the following.
INSERT INTO test_r VALUES(1,10);
INSERT 0 1
Time: 2.038 ms
Query the table after insert (expected val to be 10 since the rule would
have
been igonred as id 1 didn't exist prior to inserting.
SELECT * FROM test_r;
+----+-----+
| id | val |
+----+-----+
| 1 | 20 |
+----+-----+
(1 row)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
