BUG #5081: ON INSERT rule does not work correctly - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      5081
Logged by:          Stefan
Email address:      sb@drbott.de
PostgreSQL version: 8.3.7
Operating system:   FreeBSD 7.2
Description:        ON INSERT rule does not work correctly
Details:

I'm trying to implement an "insert_or_update" rule which  should check
whether a record with the same id already exists and if so, a UPDATE command
should be issued instead.

The problem is that if it is no record in the table, it seems that first the
INSERT command is issued and after that the UPDATE command is issued, too.
Here is the SQL code to reproduce:

create table t_test (
    count bigint,
    uid character varying(20)
);

ALTER TABLE ONLY t_test ADD CONSTRAINT t_test_pkey PRIMARY KEY (uid);
CREATE OR REPLACE RULE insert_or_update AS ON INSERT TO t_test WHERE (EXISTS
(SELECT true AS bool FROM t_test WHERE t_test.uid = new.uid)) DO INSTEAD
UPDATE t_test SET "count" = t_test."count" + new."count" WHERE t_test.uid =
new.uid;
insert into t_test VALUES (1, 'sb');
select * from t_test;

In this case, the SELECT should show a value of 1 for column "count", but it
shows 2.

Best Regards,

Stefan Baehring