Обсуждение: Clean way to insert records if they don't exist, update if they do

Here's my situation.  I have a table with a bunch of URLs and crawl
dates associated with them.  When my program processes a URL, I want
to INSERT a new row with a crawl date.  If the URL already exists, I
want to update the crawl date to the current datetime.  With MS SQL or
Oracle I'd probably use a MERGE command for this.  With mySQL I'd
probably use the ON DUPLICATE KEY UPDATE syntax.

I could do multiple queries in my program, which may or may not be
thread safe.  I could write a SQL function which has various IF...ELSE
logic.  However, for the sake of trying out Postgres features I've
never used before, I'm thinking about creating an INSERT rule -
something like this:

CREATE RULE Pages_Upsert AS ON INSERT TO Pages
   WHERE EXISTS (SELECT 1 from Pages P where NEW.Url = P.Url)
   DO INSTEAD
      UPDATE Pages SET LastCrawled = NOW(), Html = NEW.Html WHERE Url = NEW.Url;

This seems to actually work great.  It probably loses some points on
the "code readability" standpoint, as someone looking at my code for
the first time would have to magically know about this rule, but I
guess that could be solved with good code commenting and
documentation.

Are there any other drawbacks to this idea, or maybe a "your idea
sucks, you should do it /this/ way instead" comment?  I'm on PG 9.0 if
that matters.  BTW, add my name to the long list of people who would
love to see UPSERT and/or MERGE commands in the next version of PG.

Mike

Hi, first of all, I still haven't tried PG further that 8.4

2011/9/18, Mike Christensen <mike@kitchenpc.com>:
> CREATE RULE Pages_Upsert AS ON INSERT TO Pages
>    WHERE EXISTS (SELECT 1 from Pages P where NEW.Url = P.Url)
>    DO INSTEAD
>       UPDATE Pages SET LastCrawled = NOW(), Html = NEW.Html WHERE Url =
> NEW.Url;
>
> This seems to actually work great.  It probably loses some points on
> the "code readability" standpoint, as someone looking at my code for
> the first time would have to magically know about this rule, but I
> guess that could be solved with good code commenting and
> documentation.

I was in a situation like yours and did exactly what you are saying.
The insert rule and the documentation (after a week I forgot about the
matter and was surprise by the resolution I had taken when I reviewed
the comment :).

> Are there any other drawbacks to this idea, or maybe a "your idea
> sucks, you should do it /this/ way instead" comment?  I'm on PG 9.0 if
> that matters.  BTW, add my name to the long list of people who would
> love to see UPSERT and/or MERGE commands in the next version of PG.

I used that code for quiet a long time with no drawbacks; it worked
perfectly fine for me (tested it nice with a heavy load, in production
things were more quiet).
What's more, I find this approach very appropiate from the
"programming" point of view. Correct me if I'm wrong.

--
Diego Augusto Molina
diegoaugustomolina@gmail.com

ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán
desestimados.
EN: Please, avoid attaching Microsoft Office documents. They shall be discarded.
LINK: http://www.gnu.org/philosophy/no-word-attachments.html