Re: how do I update or insert efficently in postgres

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