Thread

1. Re: Bulkloading using COPY - ignore duplicates?

   Daniel Kalchev <daniel@digsys.bg> — 2002-01-04T07:36:01Z

   >>>Bruce Momjian said:
    > Mikheev, Vadim wrote:
    > > > > Bruce Momjian <pgman@candle.pha.pa.us> writes:
    > > > > > Seems nested transactions are not required if we load
    > > > > > each COPY line in its own transaction, like we do with
    > > > > > INSERT from pg_dump.
    > > > > 
    > > > > I don't think that's an acceptable answer.  Consider
    > > > 
    > > > Oh, very good point.  "Requires nested transactions" added to TODO.
    > > 
    > > Also add performance issue with per-line-commit...
    > > 
    > > Also-II - there is more common name for required feature - savepoints.
    > 
    > OK, updated TODO to prefer savepoints term.
   
   Now, how about the same functionality for
   
   INSERT into table1 SELECT * from table2 ... WITH ERRORS;
   
   Should allow the insert to complete, even if table1 has unique indexes and we 
   try to insert duplicate rows. Might save LOTS of time in bulkloading scripts 
   not having to do single INSERTs.
   
   Guess all this will be available in 7.3?
   
   Daniel
   
   
   

2. Re: Bulkloading using COPY - ignore duplicates?

   Vadim Mikheev <vmikheev@sectorbase.com> — 2002-01-04T07:47:36Z

   > Now, how about the same functionality for
   >
   > INSERT into table1 SELECT * from table2 ... WITH ERRORS;
   >
   > Should allow the insert to complete, even if table1 has unique indexes and
   we
   > try to insert duplicate rows. Might save LOTS of time in bulkloading
   scripts
   > not having to do single INSERTs.
   
   1. I prefer Oracle' (and others, I believe) way - put statement(s) in PL
   block and define
   for what exceptions (errors) what actions should be taken (ie IGNORE for
   NON_UNIQ_KEY
   error, etc).
   
   2. For INSERT ... SELECT statement one can put DISTINCT in select' target
   list.
   
   > Guess all this will be available in 7.3?
   
   We'll see.
   
   Vadim
   
   
   
   

3. Re: Bulkloading using COPY - ignore duplicates?

   Bruce Momjian <pgman@candle.pha.pa.us> — 2002-01-04T18:16:46Z

   Vadim Mikheev wrote:
   > > Now, how about the same functionality for
   > >
   > > INSERT into table1 SELECT * from table2 ... WITH ERRORS;
   > >
   > > Should allow the insert to complete, even if table1 has unique indexes and
   > we
   > > try to insert duplicate rows. Might save LOTS of time in bulkloading
   > scripts
   > > not having to do single INSERTs.
   > 
   > 1. I prefer Oracle' (and others, I believe) way - put statement(s) in PL
   > block and define
   > for what exceptions (errors) what actions should be taken (ie IGNORE for
   > NON_UNIQ_KEY
   > error, etc).
   
   Added to TODO:
   
   	* Allow command blocks that can ignore certain types of errors
   
   -- 
     Bruce Momjian                        |  http://candle.pha.pa.us
     pgman@candle.pha.pa.us               |  (610) 853-3000
     +  If your life is a hard drive,     |  830 Blythe Avenue
     +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026