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

Поиск
Список
Период
Сортировка
От Mike Christensen
Тема Clean way to insert records if they don't exist, update if they do
Дата
Msg-id CABs1bs20R_vOL5TfyO-mw-aD8SE=LhBWbnX2Rzdpd8UyAJax-g@mail.gmail.com
обсуждение исходный текст
Ответы Re: Clean way to insert records if they don't exist, update if they do
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Scott Marlowe
Дата:
Сообщение: Re: Am I best off keeping large chunks of text in a separate table?
Следующее
От: "Edson Carlos Ericksson Richter"
Дата:
Сообщение: Foreign tables using postgresql_fdw or odbc_fdw