pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Llew Goodstadt <leo.goodstadt@human-anatomy.oxford.ac.uk>
To: 'Marc Spitzer' <marc@oscar.eng.cv.net>
To: pgsql-sql@postgresql.org
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> (raw)

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




view thread (5+ messages)

Message-ID: <008c01c16cb6$319d13b0$951b4cc0@cray5>
Permalink:  ../008c01c16cb6$319d13b0$951b4cc0@cray5/
Also on:    postgresql.org/message-id/008c01c16cb6$319d13b0$951b4cc0@cray5

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: leo.goodstadt@human-anatomy.oxford.ac.uk, marc@oscar.eng.cv.net
  Subject: Re: how do I update or insert efficently in postgres
  In-Reply-To: <008c01c16cb6$319d13b0$951b4cc0@cray5>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox