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

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.Mun.Heng@wdc.com>
> Date:
> Mon, 27 Aug 2007 18:01:54 +0800
> To:
> Dimitri Fontaine <dfontaine@hi-media.com>
>
> To:
> Dimitri Fontaine <dfontaine@hi-media.com>
> CC:
> pgsql-general@postgresql.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
>