Re: on insert when ... do instead update - Mailing list pgsql-general

From Tom Lane
Subject Re: on insert when ... do instead update
Date
Msg-id 26617.1205685809@sss.pgh.pa.us
Whole thread Raw
In response to on insert when ... do instead update  (askel <dummy666@mail.ru>)
List pgsql-general
askel <dummy666@mail.ru> writes:
> Hello everybody!
> I'm having problem with postgresql 8.3 (not sure if it is related to
> this particular version). Suppose we have accounting system database
> with the following rule (no other rules are there):

> create or replace rule update_or_create_balance as
> on insert to ledger when exists (select 1 from ledger where
> account=new.account) do instead update ledger set debit=debit
> +new.debit, credit=credit+new.credit, balance=balance+new.balance
> where account=new.account;

> What could be the reason of the following considering ledger table is
> empty:

> insert into ledger values(1,100,0,100);
> select * from ledger;
>            1 | 200.00 |   0.00 | 200.00
> (1 row)

You missed reading the bit in the docs where it says that a query added
by ON INSERT is done after the original query.  In the above example,
the WHEN clause will *always* succeed.

            regards, tom lane

pgsql-general by date:

Previous
From: "Martin Gainty"
Date:
Subject: Re: how to make this database / query faster
Next
From: "Richard Broersma"
Date:
Subject: Re: how to make this database / query faster