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