Thread

1. problem with copy command

   Jaruwan Laongmal <jaruwan@gits.net.th> — 2001-04-10T08:14:32Z

   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
   
   
   

2. Re: problem with copy command

   Joel Burton <jburton@scw.org> — 2001-04-10T15:36:43Z

   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
   
   
   

3. Re: problem with copy command

   George Moga <george@dsn.ro> — 2001-04-11T09:27:21Z

   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