Thread: skipping records with duplicate key during COPY
Hi,
I am using COPY command to load a data table. When I try to insert a record with a duplicate key, I get the error message "cannot insert a duplicate key into unique index" and no data is loaded into the table. I know that this is the way the COPY command works. Now, is there a way in which I can load a table and if duplicate records do come up, write them to an error file, but still load the other good records into the table? *I think* one of accomplishing this is by performing inserts of individual records, but that would be very slow. any suggestions?
TIA,
Devinder
On Wed, 2002-12-18 at 13:16, Devinder K Rajput wrote: > Hi, > I am using COPY command to load a data table. When I try to > insert a record with a duplicate key, I get the error message "cannot > insert a duplicate key into unique index" and no data is loaded into > the table. I know that this is the way the COPY command works. Now, > is there a way in which I can load a table and if duplicate records do > come up, write them to an error file, but still load the other good > records into the table? *I think* one of accomplishing this is by > performing inserts of individual records, but that would be very slow. > any suggestions? Any method of inserting records where there is a unique index will be slow, since the index must be checked and populated. Your idea of doing individual inserts (via C, Python or Perl) is a valid one, for the exact reason you state, and because input data is not always in COPY format... -- +---------------------------------------------------------------+ | Ron Johnson, Jr. mailto:ron.l.johnson@cox.net | | Jefferson, LA USA http://members.cox.net/ron.l.johnson | | | | "My advice to you is to get married: If you find a good wife, | | you will be happy; if not, you will become a philosopher." | | Socrates | +---------------------------------------------------------------+
--- Ron Johnson <ron.l.johnson@cox.net> wrote: > On Wed, 2002-12-18 at 13:16, Devinder K Rajput > wrote: > > Hi, > > I am using COPY command to load a data > table. When I try to > > insert a record with a duplicate key, I get the > error message "cannot > > insert a duplicate key into unique index" and no > data is loaded into > > the table. I know that this is the way the COPY > command works. Now, > > is there a way in which I can load a table and if > duplicate records do > > come up, write them to an error file, but still > load the other good > > records into the table? *I think* one of > accomplishing this is by > > performing inserts of individual records, but that > would be very slow. > > any suggestions? > > Any method of inserting records where there is a > unique index will > be slow, since the index must be checked and > populated. > > Your idea of doing individual inserts (via C, Python > or Perl) is > a valid one, for the exact reason you state, and > because input > data is not always in COPY format... > What about creating a BEFORE INSERT trigger that will check for duplicate index. The trigger can insert the duplicate records into another table and "RETURN NULL" so that it will not insert into the table with unique index. ludwig. __________________________________________________ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com
On Wed, 2002-12-18 at 20:29, Ludwig Lim wrote: > --- Ron Johnson <ron.l.johnson@cox.net> wrote: > > On Wed, 2002-12-18 at 13:16, Devinder K Rajput > > wrote: > > > Hi, > > > I am using COPY command to load a data > > table. When I try to > > > insert a record with a duplicate key, I get the > > error message "cannot > > > insert a duplicate key into unique index" and no > > data is loaded into > > > the table. I know that this is the way the COPY > > command works. Now, > > > is there a way in which I can load a table and if > > duplicate records do > > > come up, write them to an error file, but still > > load the other good > > > records into the table? *I think* one of > > accomplishing this is by > > > performing inserts of individual records, but that > > would be very slow. > > > any suggestions? > > > > Any method of inserting records where there is a > > unique index will > > be slow, since the index must be checked and > > populated. > > > > Your idea of doing individual inserts (via C, Python > > or Perl) is > > a valid one, for the exact reason you state, and > > because input > > data is not always in COPY format... > > > > What about creating a BEFORE INSERT trigger that > will check for duplicate index. The trigger can insert > the duplicate records into another table and "RETURN > NULL" so that it will not insert into the table with > unique index. That's a pretty clever idea... -- +---------------------------------------------------------------+ | Ron Johnson, Jr. mailto:ron.l.johnson@cox.net | | Jefferson, LA USA http://members.cox.net/ron.l.johnson | | | | "My advice to you is to get married: If you find a good wife, | | you will be happy; if not, you will become a philosopher." | | Socrates | +---------------------------------------------------------------+