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