Re: 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(at)scw(dot)org>
Director of Information Systems, Support Center of Washington