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
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