From: | "Rhys A(dot)D(dot) Stewart" <rhys(dot)stewart(at)gmail(dot)com> |
---|---|
To: | Guillaume Lelarge <guillaume(at)lelarge(dot)info> |
Cc: | Vangelis Katsikaros <ibob17(at)yahoo(dot)gr>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: ignore errors for COPY |
Date: | 2010-11-11 13:23:26 |
Message-ID: | AANLkTik-TVQpBcDVBOfeZ8dGSwJBwQxZUgrkMf9A5-Ho@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Nov 11, 2010 at 8:05 AM, Guillaume Lelarge
<guillaume(at)lelarge(dot)info> wrote:
> Le 11/11/2010 13:01, Vangelis Katsikaros a écrit :
>> Hello
>>
>> I have postrges 8.3.12 and I have the following issue:
>>
>> I have a table
>> create table test(
>> table_id integer,
>> datetime timestamp,
>> MMSI integer,
>> lat real,
>> lng real,
>> );
>>
>> and I bulk insert data to this table with COPY.
>>
>> A tiny portion of the data in the file are wrong. For example one date
>> is "2009-93-29 05:27:08" which obviously has a wrong month (93). COPY
>> encounters this row and stop the insertion with
>> ERROR: date/time field value out of range: "2009-93-29 05:27:08"
>>
>> Is there a way I can "turn" this error into a warning (or suppress the
>> error) and make COPY simply to skip this row?
>>
>
> Nope.
>
>> I have a big amount of data (~100G) so iterating through them to find
>> all the possible wrong timestamp, reals, and integers will be quite
>> tedious and time consuming.
>>
>
> You should better look at pgloader which will use COPY to put your data
> in your table and found the lines in error. Of course, it takes time to
> detect lines in error. But at least, all "good" lines will be in your
> table, and all "bad" lines will be in a file, so that you can modify
> them to inject later.
>
>
> --
> Guillaume
> http://www.postgresql.fr
> http://dalibo.com
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
olé!!!
Maybe you could import it as a text column and then deal with the
conversion in the DB....i do that sometimes....never had 100GB of data
to work with though....
Rhys
From | Date | Subject | |
---|---|---|---|
Next Message | Vick Khera | 2010-11-11 13:30:16 | Re: good settings for DB parameters such as shared_buffers, checkpoint_segment in Postrgesql 9 |
Previous Message | Vick Khera | 2010-11-11 13:21:54 | Re: NOTIFY/LISTEN why is not a callback as notice processing. |