Re: Bulkloading using COPY - ignore duplicates?

Patrick Welche writes:
> On Thu, Dec 13, 2001 at 01:25:11PM +0000, Lee Kindness wrote:
> > That's what I'm currently doing as a workaround - a SELECT DISTINCT
> > from a temporary table into the real table with the unique index on
> > it. However this takes absolute ages - say 5 seconds for the copy
> > (which is the ballpark figure I aiming toward and can achieve with
> > Ingres) plus another 30ish seconds for the SELECT DISTINCT.
> Then your column really isn't unique,

That's another discussion entirely ;) - it's spat out by a real-time
system which doesn't have the time or resources to check this. Further
precision loss later in the data's life adds more duplicates...

> so how about dropping the unique index, import the data, fix the
> duplicates, recreate the unique index - just as another possible
> work around ;)

This is just going to be the same(ish) time, no?

CREATE TABLE tab (p1 INT, p2 INT, other1 INT, other2 INT);
COPY tab FROM 'file';
DELETE FROM tab WHERE p1, p2 NOT IN (SELECT DISTINCT p1, p2
FROM tab);
CREATE UNIQUE INDEX tab_idx ON tab USING BTREE(p1, p2);

or am I missing something?

Thanks, Lee.

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