Skip site navigation (1) Skip section navigation (2)

Re: skipping records with duplicate key during COPY

From: Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net>
To: PostgreSQL Mailing List <pgsql-novice(at)postgresql(dot)org>
Subject: Re: skipping records with duplicate key during COPY
Date: 2002-12-19 03:12:26
Message-ID: 1040267546.28774.107.camel@haggis (view raw or flat)
Thread:
Lists: pgsql-novice
On Wed, 2002-12-18 at 20:29, Ludwig Lim wrote:
> --- Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)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(dot)l(dot)johnson(at)cox(dot)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                                                   |
+---------------------------------------------------------------+


In response to

pgsql-novice by date

Next:From: ghaverlaDate: 2002-12-19 05:38:59
Subject: The pg_hba.conf file
Previous:From: Ludwig LimDate: 2002-12-19 02:29:13
Subject: Re: skipping records with duplicate key during COPY

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group