pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedimport ignoring duplicates
3+ messages / 3 participants
[nested] [flat]
* import ignoring duplicates
@ 2010-05-16 18:38 Mark Fenbers <Mark.Fenbers@noaa.gov>
0 siblings, 2 replies; 3+ messages in thread
From: Mark Fenbers @ 2010-05-16 18:38 UTC (permalink / raw)
To: pgsql-sql
I am using psql's \copy command to add records to a database from a
file. The file has over 100,000 lines. Occasionally, there is a
duplicate, and the import ceases and an internal rollback is performed.
In other words, no data is imported even if the first error occurs near
the end of the file.
I am looking for an option/switch to tell psql (or the \copy command) to
skip over any duplicate key constraint viloations and continue to load
any data that doesn't violate a duplicate key constraint. Is there such
an option?
Mark
Attachments:
Mark_Fenbers.vcf (text/x-vcard, 400B) download
begin:vcard
fn:Mark Fenbers
n:Fenbers;Mark
org:National Weather Service;Ohio River Forecast Center
adr:;;1901 South OH-134;Wilmington;OH;45177-1908;USA
email;internet:Mark.Fenbers@noaa.gov
title:Sr. HAS Meteorologist
tel;work:937-383-0430
tel;fax:937-383-0033
note:"HAS" is an acronym for Hydrometeorological Analysis and Support
x-mozilla-html:TRUE
url:http://weather.gov/ohrfc
version:2.1
end:vcard
* Re: import ignoring duplicates
@ 2010-05-17 00:32 Tim Landscheidt <tim@tim-landscheidt.de>
parent: Mark Fenbers <Mark.Fenbers@noaa.gov>
1 sibling, 0 replies; 3+ messages in thread
From: Tim Landscheidt @ 2010-05-17 00:32 UTC (permalink / raw)
To: pgsql-sql
Mark Fenbers <Mark.Fenbers@noaa.gov> wrote:
> I am using psql's \copy command to add records to a database
> from a file. The file has over 100,000 lines.
> Occasionally, there is a duplicate, and the import ceases
> and an internal rollback is performed. In other words, no
> data is imported even if the first error occurs near the end
> of the file.
> I am looking for an option/switch to tell psql (or the \copy
> command) to skip over any duplicate key constraint
> viloations and continue to load any data that doesn't
> violate a duplicate key constraint. Is there such an
> option?
No. You can either disable the constraint temporarily, im-
port the data, fix any duplicates and re-enable the con-
straint, or you can load the data in a temporary table and
then transfer only the valid data. With only 100000 records
I would opt for the latter.
Tim
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: import ignoring duplicates
@ 2010-05-17 06:04 Scott Marlowe <scott.marlowe@gmail.com>
parent: Mark Fenbers <Mark.Fenbers@noaa.gov>
1 sibling, 0 replies; 3+ messages in thread
From: Scott Marlowe @ 2010-05-17 06:04 UTC (permalink / raw)
To: Mark Fenbers <Mark.Fenbers@noaa.gov>; +Cc: pgsql-sql
On Sun, May 16, 2010 at 12:38 PM, Mark Fenbers <Mark.Fenbers@noaa.gov> wrote:
> I am using psql's \copy command to add records to a database from a file.
> The file has over 100,000 lines. Occasionally, there is a duplicate, and
> the import ceases and an internal rollback is performed. In other words, no
> data is imported even if the first error occurs near the end of the file.
>
> I am looking for an option/switch to tell psql (or the \copy command) to
> skip over any duplicate key constraint viloations and continue to load any
> data that doesn't violate a duplicate key constraint. Is there such an
> option?
Sounds like you want this:
http://pgfoundry.org/projects/pgloader/
Note that copy is optimized to work in a single transaction. Breaking
those semantics WILL result in a slow load time, and there's not much
you can do about that.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-05-17 06:04 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-05-16 18:38 import ignoring duplicates Mark Fenbers <Mark.Fenbers@noaa.gov>
2010-05-17 00:32 ` Tim Landscheidt <tim@tim-landscheidt.de>
2010-05-17 06:04 ` Scott Marlowe <scott.marlowe@gmail.com>
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