Skip site navigation (1) Skip section navigation (2)

BUG #5081: ON INSERT rule does not work correctly

From: "Stefan" <sb(at)drbott(dot)de>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5081: ON INSERT rule does not work correctly
Date: 2009-09-25 08:14:55
Message-ID: 200909250814.n8P8EtY4048711@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      5081
Logged by:          Stefan
Email address:      sb(at)drbott(dot)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

Responses

pgsql-bugs by date

Next:From: Jaime CasanovaDate: 2009-09-25 14:19:41
Subject: diferent timezones in the same table?
Previous:From: Annita VenetiDate: 2009-09-25 07:56:22
Subject: Re: BUG #5063: MS Access crashes by quiting after linking tables with PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group