Re: ignore errors for COPY

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