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

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

2011/9/18, Mike Christensen <mike(at)kitchenpc(dot)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(at)gmail(dot)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