Re: Bulkloading using COPY - ignore duplicates?

Hannu Krosing writes:
> Lee Kindness wrote:
> > The majority of database systems out there handle this situation in
> > one manner or another (MySQL ignores or replaces; Ingres ignores;
> > Oracle ignores or logs; others...). Indeed PostgreSQL currently checks
> > for duplicates in the COPY code but throws an elog(ERROR) rather than
> > ignoring the row, or passing the error back up the call chain.
> I guess postgresql will be able to do it once savepoints get
> implemented.

This is encouraging to hear. I can see how this would make the code
changes relatively minimal and more manageable - the changes to the
current code are simply over my head!

Are savepoints relatively high up on the TODO list, once 7.2 is out the
door?

> > My use of PostgreSQL is very time critical, and sadly this issue alone
> > may force an evaluation of Oracle's performance in this respect!
> Can't you clean the duplicates _outside_ postgresql, say
> cat dumpfile | sort | uniq | psql db -c 'copy mytable from stdin'

This is certainly a possibility, however it's just really moving the
processing elsewhere. The combined time is still around the same.

I've/we've done a lot of investigation with approaches like this and
also with techniques assuming the locality of the duplicates (which is
a no-goer). None improve the situation.

I'm not going to compare the time of just using INSERTs rather than
COPY...

Thanks for your response, Lee Kindness.

--
Lee Kindness, Senior Software Engineer, Concept Systems Limited.
http://services.csl.co.uk/ http://www.csl.co.uk/ +44 131 5575595