Эмулируем ON DUPLICATE KEY UDPATE — подводный камень

В MySQL можно делать INSERT… ON DUPLICATE KEY UDPATE. В постгресе пока что такого нет (насколько мне известно, MERGE не вошел в версию 8.4).

Я натыкался в сети на рекомендацию использовать правила для того чтобы добиться аналогичного поведения. Мне показалось, что использовать правила в данном случае даже лучше, нежели MERGE, я сделал правило… и наткнулся на подводный камень. Я реализовывал счетчик: есть уникальный ключ и, если такой ключ в таблице уже существует при вставке новой записи — следует вместо этого увеличить значение счетчика.

Простая ситуация, простое правило. Но: при первой вставке это правило, как оказалось, тоже отрабатывает! В результате, при первой вставке вставляется не 1, а 2!

Пока писал, Fred84 подкинул ссылку, где об этой ситуации подробнее рассказано: 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

Как видите, counter=2, хотя по логике вещей должен быть 1.