Re: on insert when ... do instead update

askel <dummy666(at)mail(dot)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