On Tue, Jun 22, 2004 at 08:51:07PM +0100, Simon Riggs wrote:
> > Why is it better than COPY?
> COPY is designed for bulk data loading from files etc. Extended INSERTs
> are used to minimise the number of round-trips to the database when
> issuing a few number of similar INSERTs, as when you do an
> Order/Order-Line (i.e. Master and many similar Details records).
Huh? I think you should be able to issue a single multivalued insert to
a single table -- two queries in your Order/Order-Line example, not one.
You can do the same in Postgres issuing several commands separated with
semicolons. You can even use a single transaction by using BEGIN and
COMMIT at the start and end of the string.
I think the real gain of multivalued insert will come from using batch
heap and index insertions, not roundtrip to the application. At least,
the roundtrip savings can be had with today's Postgres.
> On the technical side, I think we should have both extended INSERT and
> REPLACE on the TODO list...
I don't think REPLACE is a good idea (useless non standard MySQL-ism).
MERGE and multivalued INSERT are. But they depend on someone actually
doing the work. Both are on the TODO list, I think.
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Siempre hay que alimentar a los dioses, aunque la tierra esté seca" (Orual)
