Re: Bulk Load Ignore/Skip Feature - Mailing list pgsql-general

On Wed, 2007-11-14 at 00:02 -0800, Willem Buitendyk wrote:
> Perfect - that appears to be exactly what I was looking for.

> Reg Me Please wrote:
> > Il Wednesday 14 November 2007 05:50:36 Willem Buitendyk ha scritto:
> >
> >> Will Postgresql ever implement an ignore on error feature when bulk
> >> loading data?  Currently it is my understanding that any record that
> >> violates a unique constraint will cause the "copy from" command to halt
> >> execution instead of skipping over the violation and logging it - as is
> >> done in Oracle and DB2.
> >
> > pgloader
> >
> > http://pgfoundry.org/projects/pgloader/
> >
> >

I believe the last time I tried this, there was still some issues with
it. See attached email. (if it makes it to the list)

On Mon, 2007-08-27 at 11:27 +0200, Dimitri Fontaine wrote:

> We've just made some tests here with 2.2.1 and as this release contains the
> missing files, it works fine without any installation.

Yep.. I can confirm that it works.. I am using the csv example.

Goal : similar functionality much like mysql's mysqlimport --replace
(overwrite any rows which has duplicate primary keys)

$ psql pgloader < csv/csv.sql
$ ../pgloader.py -Tvc examples/pgloader.conf csv

pgloader=# alter table csv add primary key (a,b,c);
pgloader=# \d csv
        Table "public.csv"
 Column |     Type     | Modifiers
--------+--------------+-----------
 a      | bigint       | not null
 b      | bigint       | not null
 c      | character(2) | not null
 d      | text         |
Indexes:
    "csv_pkey" PRIMARY KEY, btree (a, b, c)

pgloader=# select * from csv;
    a     |    b     | c  |       d
----------+----------+----+----------------
 33996344 | 33996351 | GB | United Kingdom
 50331648 | 68257567 | US | United States
 68257568 | 68257599 | CA | Canada
 68257600 | 68259583 | US | United States
 68259584 | 68259599 | CA | Canada

$cat csv/csv.data
"2.6.190.56","2.6.190.63","33996344","33996351","GB","Error Kingdom"
"4.17.143.0","4.17.143.15","68259584","68259599","CA","new Country"
<Note : only columns 3 to 6 are taken for loading)

$ psql pgloader < csv/csv.sql
$ ../pgloader.py -vc pgloader.conf csv
Using pgloader.conf configuration file
Will consider following sections:
  csv

[csv] parse configuration
Notice: reject log in /tmp/csv.rej.log
Notice: rejected data in /tmp/csv.rej
[csv] data import
Notice: COPY csv data

Error: Please check PostgreSQL logs
HINT:  double check your client_encoding, datestyle and copy_delimiter
settings

$sudo tail -f /var/log/pglog/postgresxx-xx-xx.log
ERROR:  duplicate key violates unique constraint "csv_pkey"
CONTEXT:  COPY csv, line 1: "33996344,33996351,Error Kingdom,GB"
STATEMENT:  COPY csv (a, b, d, c)  FROM stdin USING DELIMITERS ','

So.. doesn't really solve my issue.
Dang it..


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings