Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fADMbim55025 for ; Tue, 13 Nov 2001 17:37:44 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id fADMRi737035 for pgsql-sql@postgresql.org; Tue, 13 Nov 2001 17:27:45 -0500 (EST) (envelope-from news) X-Newsgroups: comp.databases.postgresql.sql From: marc@oscar.eng.cv.net (Marc Spitzer) Subject: Re: how do I update or insert efficently in postgres References: <3557.1005685484@sss.pgh.pa.us> Message-Id: User-Agent: slrn/0.9.7.0 (FreeBSD) Lines: 75 Date: Tue, 13 Nov 2001 22:27:37 GMT Organization: Optimum Online To: pgsql-sql@postgresql.org X-Archive-Number: 200111/176 X-Sequence-Number: 5560 In article <3557.1005685484@sss.pgh.pa.us>, Tom Lane wrote: > marc@oscar.eng.cv.net (Marc Spitzer) writes: >> I need to do the follwoing logic for a db I am building: >> if row exists update some fields >> else insert all fields > >> I have come across this befor and have used select to drive the >> choice, if I could get the row update else insert. > > Which case do you think will be more common? If UPDATE is the more > common scenario then it's a win to do > > UPDATE set modifiable-fields = whatever WHERE key = whatever > if (zero rows updated) > INSERT ... > > Alternatively you can do > > INSERT ... > if (fail due to duplicate key) > UPDATE ... > > if you think INSERT is the more common case. (This all assumes you > have a unique key for the table, but if you don't, then what do you > mean by "the row already exists"?) > > Neither of these are perfect, however. The former has a race condition > if two clients might try to insert the same key at about the same time. > You can improve it to > > BEGIN; > UPDATE set modifiable-fields = whatever WHERE key = whatever > if (zero rows updated) > { > INSERT ... > if (fail due to duplicate key) > { > ABORT; > loop back to BEGIN; > } > } > COMMIT; > > but this is kinda ugly. (Of course, if you could have two clients > independently inserting/updating the same row at the same time, you > have problems anyway: which one should win, and why? I think the > coding difficulty may tell you you have a design problem.) > > As for the INSERT-then-UPDATE approach, you have the same problem > that you have to ABORT and start a new transaction if the INSERT > fails. This is uncool if you really want the whole thing to be part > of a larger transaction. > > But as long as you've guessed right about which case is more common, > you have only one query most of the time. > > regards, tom lane > after the first pass it will be mostly updates and I know I have a flawed design, but I have only 1 batch job that loads information so there should be no major problems there, yes I know famious last words. And I need to get something finished quickly to start generating reports off of it. Thanks marc > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly