On 2006-01-03, Tony Wasson <[EMAIL PROTECTED]> wrote: > On 1/2/06, Julian Scarfe <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
