Re: Bulkloading using COPY - ignore duplicates?

Lee Kindness wrote:
> Tom Lane writes:
>  > Lee Kindness <lkindness@csl.co.uk> writes:
>  > > In an ideal world 'COPY FROM' would only be used with data output by
>  > > 'COPY TO' and it would be nice and sanitised. However in some fields
>  > > this often is not a possibility due to performance constraints!
>  > Of course, the more bells and whistles we add to COPY, the slower it
>  > will get, which rather defeats the purpose no?
> 
> Indeed, but as I've mentioned in this thread in the past, the code
> path for COPY FROM already does a check against the unique index (if
> there is one) but bombs-out rather than handling it...
> 
> It wouldn't add any execution time if there were no duplicates in the
> input!

I know many purists object to allowing COPY to discard invalid rows in
COPY input, but it seems we have lots of requests for this feature, with
few workarounds except pre-processing the flat file.  Of course, if they
use INSERT, they will get errors that they can just ignore.  I don't see
how allowing errors in COPY is any more illegal, except that COPY is one
command while multiple INSERTs are separate commands.

Seems we need to allow such a capability, if only crudely.  I don't
think we can create a discard file because of the problem with remote
COPY.

I think we can allow something like:
COPY FROM '/tmp/x' WITH ERRORS 2

meaning we will allow at most two errors and will report the error line
numbers to the user.  I think this syntax clearly indicates that errors
are being accepted in the input.  An alternate syntax would allow an
unlimited number of errors:
COPY FROM '/tmp/x' WITH ERRORS

The errors can be non-unique errors, or even CHECK constraint errors.

Unless I hear complaints, I will add it to TODO.

--  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,
Pennsylvania19026