On Tue, Jun 22, 2004 at 10:14:11PM +0100, Simon Riggs wrote:
> On Tue, 2004-06-22 at 21:05, Alvaro Herrera wrote:
> > 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.
>
> Better explained, that would be:
> **If you're doing an Order/Order-Line insert you'll do 1 INSERT to Order
> and then multiple similar INSERTs to Order-Line: the multiple INSERTs
> into Order-Line are when you'd use the extended INSERT feature.**
Yeah, I thought you could mean that.
Another nicety of multivalued insert compared to COPY is that you could
use currval() of a sequence that you just used in the insert to the
Order (of course, you can do that with a multiquery string too).
> > 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.
>
> Yes, there are PostgreSQL ways of doing things, not my point, I was
> explaining the MySQL rationale as I saw it.
Ah, ok.
> > 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.
>
> I think doing REPLACE is a trivial subset of MERGE, so should be easy
> enough to pull off once MERGE gets done.
Hmm ... and what if later the SQL committee adds a (different) REPLACE
command? Anyway, Postgres doesn't follow MySQL's lead ... some people
here wants to rule the database world first and become the SQL editors
next. *chuckle*
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Pido que me den el Nobel por razones humanitarias" (Nicanor Parra)
