Clean way to insert records if they don't exist, update if they do

From: Mike Christensen <mike(at)kitchenpc(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Clean way to insert records if they don't exist, update if they do
Date: 2011-09-18 04:22:15
Message-ID: CABs1bs20R_vOL5TfyO-mw-aD8SE=LhBWbnX2Rzdpd8UyAJax-g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Edson Carlos Ericksson Richter 2011-09-18 13:32:43 Foreign tables using postgresql_fdw or odbc_fdw
Previous Message Scott Marlowe 2011-09-18 01:20:37 Re: Am I best off keeping large chunks of text in a separate table?