Re: skipping records with duplicate key during COPY

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                                                   |
+---------------------------------------------------------------+