Received: from oxmail.ox.ac.uk (oxmail1.ox.ac.uk [129.67.1.2]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAE2j1m66395 for ; Tue, 13 Nov 2001 21:45:02 -0500 (EST) (envelope-from leo.goodstadt@human-anatomy.oxford.ac.uk) Received: from heraldgate2.oucs.ox.ac.uk ([163.1.2.50] helo=frontend2.herald.ox.ac.uk ident=exim) by oxmail.ox.ac.uk with esmtp (Exim 3.33 #3) id 163q2W-00023v-01; Wed, 14 Nov 2001 02:44:36 +0000 Received: from max149.public.ox.ac.uk ([192.76.27.149] helo=cray5) by frontend2.herald.ox.ac.uk with esmtp (Exim 3.32 #1) id 163q2S-0004co-00; Wed, 14 Nov 2001 02:44:33 +0000 Reply-To: From: "Llew Goodstadt" To: "'Marc Spitzer'" , Subject: Re: how do I update or insert efficently in postgres Date: Wed, 14 Nov 2001 02:43:50 -0000 Message-ID: <008c01c16cb6$319d13b0$951b4cc0@cray5> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Importance: Normal X-Archive-Number: 200111/201 X-Sequence-Number: 5585 What if you inserted all the records into a temporary table. Delete all matching records (which would have been updated)in the current table (via a join). Then copy all the entries from the temporary table into your existing table. Delete the temp table etc. Llew > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Marc Spitzer > Sent: 13 November 2001 18:19 > To: pgsql-sql@postgresql.org > Subject: [SQL] how do I update or insert efficently in postgres > > > 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. The > db I worked on had a few thousand rows so it was fast enough. > This table will have around 1 million rows to start out with > and I was wondering if there was any way to do this better. > I am touching each row twice and would like to get that down > to once if possable. If that is not possable would it be > better to move the whole thing inside of 1 explicit > transaction? Any other ideas I have missed? > > Thank you > > marc > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > http://www.postgresql.org/users-lounge/docs/faq.html