Emulate ON DUPLICATE KEY UDPATE - Reef

In MySQL, you can do INSERT ... ON DUPLICATE KEY UDPATE. In the postgrese, there is no such thing yet (as far as I know, MERGE was not included in version 8.4).

I stumbled across the network on a recommendation to use the rules in order to achieve similar behavior. It seemed to me that using the rules in this case is even better than MERGE, I made a rule ... and came across a cave. I implemented the counter: there is a unique key and, if such a key in the table already exists when inserting a new record, you should instead increase the value of the counter.

Simple situation, simple rule. But: when you first insert this rule, as it turned out, it also works out! As a result, at the first insertion, not 1, but 2 is inserted!
')
While writing, Fred84 threw a link where it is described in more detail about this situation: archives.postgresql.org/pgsql-bugs/2007-03/msg00080.php

  # CREATE TABLE x (id INT NOT NULL, counter INT NOT NULL DEFAULT 0, PRIMARY KEY (id));
 # CREATE OR REPLACE RULE xxx AS
     ON INSERT TO x
    WHERE (EXISTS (SELECT x.id
            FROM x
           WHERE x.id = new.id)) DO INSTEAD UPDATE x SET counter = x.counter + 1
   WHERE x.id = new.id;
 # INSERT INTO x VALUES (1, 1);
 # select * from x;
  id |  counter
 ---- + ---------
   1 |  2 

As you can see, counter = 2, although the logic of things should be 1.