Re: Rule acting as REPLACE INTO behave strange

"IVO GELOV" <ivo_gelov(at)abv(dot)bg> writes:
> I have a rule which mimics REPLACE INTO. The table is defined like this

People keep thinking they can use rules for that :-(

You can't do this. The problem in your case is that the INSERT happens,
and then the rule fires and finds the inserted row, which it happily
updates. See the manual's explanation:
http://www.postgresql.org/docs/8.3/static/rules-update.html

There are all sorts of other gotchas if you get past that one.
You might be able to make it work if you use a BEFORE INSERT trigger
instead, but a rule really isn't going to get the job done.

Oh, one bit of other advice: don't even *think* of identifying a
particular row to be updated-rather-than-inserted using a criterion that
doesn't correspond to a primary key value. Those coalesce conditions
you're using are guaranteed to make you cry later, because they mean
that you could have several rows in the table that your logic thinks
are the same row. You really need a backup constraint to enforce
that there is only one such row.

regards, tom lane