Re: INSERT OR UPDATE

On 2006-01-03, Tony Wasson <ajwasson(at)gmail(dot)com> wrote:
> On 1/2/06, Julian Scarfe <julian(at)avbrief(dot)com> wrote:
>> I'm keeping config information for an application in a series of related
>> tables. I'd like a command that INSERTs data if it's new, or UPDATEs it if
>> the key is duplicated.
>
> A MERGE trigger will do exactly what you are asking for.
>
> http://archives.postgresql.org/pgsql-sql/2004-05/msg00135.php
>
> Send all your data as INSERTS.
> The trigger will run a function to see if the row exists.
> If the row exists -> rewrite it as it as an UPDATE
> If the row does not exist -> leave the INSERT alone

There are some rather serious race conditions in that (it's not taking
anything like a strong enough lock to avoid that - it would need to use
SHARE ROW EXCLUSIVE, not ROW EXCLUSIVE).

In 8.0 on, use the method described here:
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services