Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fADL4wm50847 for ; Tue, 13 Nov 2001 16:04:59 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id fADL4ic03560; Tue, 13 Nov 2001 16:04:45 -0500 (EST) To: marc@oscar.eng.cv.net (Marc Spitzer) cc: pgsql-sql@postgresql.org Subject: Re: how do I update or insert efficently in postgres In-reply-to: References: Comments: In-reply-to marc@oscar.eng.cv.net (Marc Spitzer) message dated "Tue, 13 Nov 2001 18:18:34 +0000" Date: Tue, 13 Nov 2001 16:04:44 -0500 Message-ID: <3557.1005685484@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200111/173 X-Sequence-Number: 5557 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