Обсуждение: INSERT or COPY: Which one?

Dear All,

I which to insert text (more than 1000 lines) into a single cell of my table, should I use COPY here or INSERT?

My reading seems to suggest that you would use COPY for mulitple INSERTs, but when inserting alot of data into one
cell,INSERT is fine. Is this correct? 

Thanks

Colin

Colin Gillespie wrote:
> Dear All,
>
> I which to insert text (more than 1000 lines) into a single cell of my table, should I use COPY here or INSERT?
>
> My reading seems to suggest that you would use COPY for mulitple INSERTs, but when inserting alot of data into one
cell,INSERT is fine. Is this correct? 

COPY is much faster, INSERT is safer.
I heard that all issues regarding COPY are fixed (since 7.3?), but IIRC I still had some trouble
with SERIALs and referential integrity.

HTH
--
  e-Trolley Sayegh & John, Nabil Sayegh
  Tel.: 0700 etrolley /// 0700 38765539
  Fax.: +49 69 8299381-8
  PGP : http://www.e-trolley.de

Hi Colin,

COPY is much faster that INSERT, although INSERT is safer. I think that
you can speed up the INSERT by disabling autocommit...


Cheers!
Wim

On Mon, 16 Feb 2004, Colin Gillespie wrote:

> Dear All,
>
> I which to insert text (more than 1000 lines) into a single cell of my table, should I use COPY here or INSERT?
>
> My reading seems to suggest that you would use COPY for mulitple INSERTs, but when inserting alot of data into one
cell,INSERT is fine. Is this correct? 
>
> Thanks
>
> Colin
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

I got this today and I don't know the if the list is behind or the
senders client, so I'll reply anyway.

I did some benchmark testing of copy vs insert, using a 3 million record
file with 1 table and 3 fields containing 10 numbers (between all
fields, they were phonenumbers to be precise).  The insert took about 2
hours (this was last summer so my numbers might be slightly off) and the
copy took 45 seconds.

So use copy when appropriate.

--Bryan

On Tue, 2004-02-17 at 12:01, Nabil Sayegh wrote:
> Colin Gillespie wrote:
> > Dear All,
> >
> > I which to insert text (more than 1000 lines) into a single cell of my table, should I use COPY here or INSERT?
> >
> > My reading seems to suggest that you would use COPY for mulitple INSERTs, but when inserting alot of data into one
cell,INSERT is fine. Is this correct? 
>
> COPY is much faster, INSERT is safer.
> I heard that all issues regarding COPY are fixed (since 7.3?), but IIRC I still had some trouble
> with SERIALs and referential integrity.
>
> HTH