pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedproblem 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