On Thu, Nov 11, 2010 at 8:05 AM, Guillaume Lelarge <guillaume@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) > 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
