pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
problem with copy command
4+ messages / 4 participants
[nested] [flat]

* problem with copy command
@ 2001-04-10 08:14  Jaruwan Laongmal <jaruwan@gits.net.th>
  0 siblings, 2 replies; 4+ messages in thread

From: Jaruwan Laongmal @ 2001-04-10 08:14 UTC (permalink / raw)
  To: pgsql-hackers@postgresql.org; pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: problem with copy command
@ 2001-04-10 15:36  Joel Burton <jburton@scw.org>
  parent: Jaruwan Laongmal <jaruwan@gits.net.th>
  1 sibling, 0 replies; 4+ messages in thread

From: Joel Burton @ 2001-04-10 15:36 UTC (permalink / raw)
  To: Jaruwan Laongmal <jaruwan@gits.net.th>; +Cc: pgsql-hackers@postgresql.org; pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: problem with copy command
@ 2001-04-11 09:27  George Moga <george@dsn.ro>
  parent: Jaruwan Laongmal <jaruwan@gits.net.th>
  1 sibling, 1 reply; 4+ messages in thread

From: George Moga @ 2001-04-11 09:27 UTC (permalink / raw)
  To: Jaruwan Laongmal <jaruwan@gits.net.th>; pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: problem with copy command
@ 2001-04-11 11:59  Oliver Elphick <olly@lfix.co.uk>
  parent: George Moga <george@dsn.ro>
  0 siblings, 0 replies; 4+ messages in thread

From: Oliver Elphick @ 2001-04-11 11:59 UTC (permalink / raw)
  To: Jaruwan Laongmal <jaruwan@gits.net.th>; pgsql-sql

Here is a method of filtering out the duplicate keys from the input file:

Let us assume that the input data is in /tmp/table.in, that that file is
tab-delimited and that the primary key is in field 2.

    psql -d database -c "COPY table TO '/tmp/table.1'"
    psql -d database -c "COPY table TO '/tmp/table.2'"
    cat /tmp/table.in /tmp/table.[12] |
        sort -k 2 -t \[tab] |
        uniq -u -W 1 -f 1 -t \[tab] >/tmp/table.in.unique

[tab] stands for the sequence "ctrl-V tab", which will force an actual tab
character into the command line (the backslash will protect it from the
shell).

We copy the database out twice to ensure that all keys already in it are
excluded, otherwise we would be reintroducing all primary keys that were
_not_ in the desired input.

The end product is a file that excludes all primary keys that are already
in the target table.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Is any one of you in trouble? He should pray. Is
      anyone happy? Let him sing songs of praise. Is any one
      of you sick? He should call the elders of the church
      to pray over him...The prayer of a righteous man is
      powerful and effective."         James 5:13,14,16 





^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2001-04-11 11:59 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-10 08:14 problem with copy command Jaruwan Laongmal <jaruwan@gits.net.th>
2001-04-10 15:36 ` Joel Burton <jburton@scw.org>
2001-04-11 09:27 ` George Moga <george@dsn.ro>
2001-04-11 11:59   ` Oliver Elphick <olly@lfix.co.uk>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox