From: Stephen Frost <sfrost@snowman.net>
To: Markus Schaber <schabios@logi-track.com>
Cc: PostgreSQL Performance List <pgsql-performance@postgresql.org>
Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres --
Date: Tue, 14 Sep 2004 10:33:03 -0400
Message-ID: <20040914143303.GR21419@ns.snowman.net> (raw)
In-Reply-To: <20040914141452.002e5d4e@kingfisher.intern.logi-track.com>
References: <20040912042442.49915.qmail@web41508.mail.yahoo.com>
<pB21d.193816$X12.122954@edtnps84>
<20040914141452.002e5d4e@kingfisher.intern.logi-track.com>
* Markus Schaber (schabios@logi-track.com) wrote:
> Generally, what is the fastest way for doing bulk processing of
> update-if-primary-key-matches-and-insert-otherwise operations?
This is a very good question, and I havn't seen much of an answer to it
yet. I'm curious about the answer myself, actually. In the more recent
SQL specs, from what I understand, this is essentially what the 'MERGE'
command is for. This was recently added and unfortunately is not yet
supported in Postgres. Hopefully it will be added soon.
Otherwise, what I've done is basically an update followed by an insert
using outer joins. If there's something better, I'd love to hear about
it. The statements looks something like:
update X
set colA = a.colA,
colB = a.colB
from Y a
where keyA = a.keyA and
keyB = a.keyB;
insert into X
select a.keyA,
a.keyB,
a.colA,
a.colB
from Y a left join X b
using (keyA, keyB)
where b.keyA is NULL and
b.keyB is NULL;
With the appropriate indexes, this is pretty fast but I think a merge
would be much faster.
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 188B) download
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-performance@postgresql.org
Cc: sfrost@snowman.net, schabios@logi-track.com
Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres --
In-Reply-To: <20040914143303.GR21419@ns.snowman.net>
* 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