Date: Tue, 14 Sep 2004 10:33:03 -0400
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 --
Message-ID: <20040914143303.GR21419@ns.snowman.net>
Mail-Followup-To: Markus Schaber <schabios@logi-track.com>,
	PostgreSQL Performance List <pgsql-performance@postgresql.org>
References: <20040912042442.49915.qmail@web41508.mail.yahoo.com>
	<pB21d.193816$X12.122954@edtnps84>
	<20040914141452.002e5d4e@kingfisher.intern.logi-track.com>
Mime-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="RF5hBp8jldNhGZp7"
Content-Disposition: inline
In-Reply-To: <20040914141452.002e5d4e@kingfisher.intern.logi-track.com>
User-Agent: Mutt/1.5.5.1+cvs20040105i

--RF5hBp8jldNhGZp7
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

* Markus Schaber (schabios@logi-track.com) wrote:
> Generally, what is the fastest way for doing bulk processing of=20
> 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 =3D a.colA,
      colB =3D a.colB
  from Y a
  where keyA =3D a.keyA and
        keyB =3D 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

--RF5hBp8jldNhGZp7
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQFBRwEfrzgMPqB3kigRAo4/AJ9HyzNvLF9BFRzjAs3WEgRy9h1LuACeMQFh
Cl9BjMh9bkPnj53iJVWAIys=
=P65B
-----END PGP SIGNATURE-----

--RF5hBp8jldNhGZp7--