Re: Bulk Load Ignore/Skip Feature

Damn - so the unqiue contraint is still an issue. What gives? Why is
it so hard to implement this in Postgresql? sigh - if only I had more time.

Ow Mun Heng wrote:
> 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)
>
>
>
>
> __________ NOD32 2657 (20071114) Information __________
>
> This message was checked by NOD32 antivirus system.
> http://www.eset.com
>
>
> ------------------------------------------------------------------------
>
> Subject:
> PgLoader unable to handle pkey dups Was [Re: {Spam} [GENERAL] pgloader
> - Can't find textreader/csvreader]
> From:
> Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)com>
> Date:
> Mon, 27 Aug 2007 18:01:54 +0800
> To:
> Dimitri Fontaine <dfontaine(at)hi-media(dot)com>
>
> To:
> Dimitri Fontaine <dfontaine(at)hi-media(dot)com>
> CC:
> pgsql-general(at)postgresql(dot)org
>
>
> 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
>