Re: ignore errors for COPY

Поиск
Список
Период
Сортировка
От Guillaume Lelarge
Тема Re: ignore errors for COPY
Дата
Msg-id 4CDBEA0B.1000802@lelarge.info
обсуждение исходный текст
Ответ на ignore errors for COPY  (Vangelis Katsikaros <ibob17@yahoo.gr>)
Ответы Re: ignore errors for COPY  ("Rhys A.D. Stewart" <rhys.stewart@gmail.com>)
Re: ignore errors for COPY [solved]  (Vangelis Katsikaros <ibob17@yahoo.gr>)
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: Vangelis Katsikaros
Дата:
Сообщение: ignore errors for COPY
Следующее
От: Vick Khera
Дата:
Сообщение: Re: 2PC w/ dblink