Skip site navigation (1) Skip section navigation (2)

SQL COPY syntax extension (was: Performance on inserts)

From: Hannu Krosing <hannu(at)tm(dot)ee>
To: Oliver Teuber <teuber(at)abyss(dot)devicen(dot)de>
Cc: Matthew Kirkwood <matthew(at)hairy(dot)beasts(dot)org>, pgsql-hackers(at)postgresql(dot)org
Subject: SQL COPY syntax extension (was: Performance on inserts)
Date: 2000-08-28 10:05:19
Message-ID: 39AA395F.3E400673@tm.ee (view raw or flat)
Thread:
Lists: pgsql-hackers
Oliver Teuber wrote:
> 
> On Sat, Aug 26, 2000 at 12:14:06PM +0100, Matthew Kirkwood wrote:
> > On Sat, 26 Aug 2000, Jules Bean wrote:
> >
> > > Is there any simple way for Pg to combine inserts into one bulk?
> > > Specifically, their effect on the index files.  It has always seemed
> > > to me to be one of the (many) glaring flaws in SQL that the INSERT
> > > statement only takes one row at a time.
> >
> > One of MySQL's little syntax abuses allows:
> >
> > INSERT INTO tab (col1, ..) VALUES (val1, ..), (val2, ..);
> >
> > which is nice for avoiding database round trips.  It's one
> > of the reasons that mysql can do a bulk import so quickly.
> >
> copy seems to be very fast ... i dont know why all people use
> multiple inserts ??? just use copy ...
>

Could copy be extended to support a more SQL-friendly syntax. like

COPY tablename FROM VALUES(
 (x1,y1,z1),
 (x2,y2,z2),
 (x3,y3,z3)
);

The main reason I have not used COPY very much is it's non-SQL syntax 
for field values.

Extending the COPY command would probably be much easier than speeding up
INSERTS.

-------------
Hannu

In response to

Responses

pgsql-hackers by date

Next:From: Zeugswetter Andreas SBDate: 2000-08-28 10:18:30
Subject: AW: AW: How Do You Pronounce "PostgreSQL"?
Previous:From: Tom LaneDate: 2000-08-28 04:57:32
Subject: Re: Too many open files (was Re: spinlock problems reported earlier)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group