dear all,
I currently using postgresql v7.0.3
when i import text file to table with command "copy tablename from
'/tmp/a.txt';
and it shows
"copy: line 20, Cannot insert a duplicate key into unique index testpri_pk"
,then it exits with doing nothing.

I want to ignore this errors and continue copy the next record. How to do
that?
if I don't filter in '/tmp/a.txt' before using copy command.

Thank you so much for your help in advance .
Regards
Jaruwan


Tue, 10 Apr 2001 15:14:32 +0700
"Jaruwan Laongmal" <jaruwan@gits.net.th>
problem with copy command 
On Tue, 10 Apr 2001, Jaruwan Laongmal wrote:

> dear all,
> I currently using postgresql v7.0.3
> when i import text file to table with command "copy tablename from
> '/tmp/a.txt';
> and it shows
> "copy: line 20, Cannot insert a duplicate key into unique index testpri_pk"
> ,then it exits with doing nothing.
> 
> I want to ignore this errors and continue copy the next record. How to do
> that?
> if I don't filter in '/tmp/a.txt' before using copy command.

AFAIK, you can't ignore primary keys, so you can't cheat and get it in,
even for a moment. And if COPY encounters bad data, it ends the
transaction. (Both of these seem like the Right Thing to me, though
perhaps there's an argument for COPY IGNORING ERRORS or something like
that. Ick.)


Either

1) filter /tmp/a.txt to remove duplicates

or 

2) drop your unique index, copy the data, get rid of duplicates, the add
the index again

or

2) 

Assuming your table you're importing to is

  CREATE TABLE names (lname text, fname text, primary key (lname,
fname) );

Create another table w/o the primary key:

 CREATE TABLE import (lname text, fname text);

copy to *this* table, then copy from this table to the names table,
ignoring duplicates in the import:

 SELECT distinct fname, lname into names from import;

-- 
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington


Tue, 10 Apr 2001 11:36:43 -0400 (EDT)
Joel Burton <jburton@scw.org>
Re: problem with copy command 
Jaruwan Laongmal wrote:

> dear all,
> I currently using postgresql v7.0.3
> when i import text file to table with command "copy tablename from
> '/tmp/a.txt';
> and it shows
> "copy: line 20, Cannot insert a duplicate key into unique index testpri_pk"
> ,then it exits with doing nothing.
>
> I want to ignore this errors and continue copy the next record. How to do
> that?
> if I don't filter in '/tmp/a.txt' before using copy command.
>
> Thank you so much for your help in advance .
> Regards
> Jaruwan

Try to delete the unique index testpri_pk ... but if you want to create the
unique index again you must delete (or modify) you'r not_unique rows.


George Moga,
    Data Systems Srl
    Slobozia, ROMANIA



Wed, 11 Apr 2001 12:27:21 +0300
George Moga <george@dsn.ro>
Re: problem with copy command