Re: error-tolerant COPY FROM

On Fri, 4 Feb 2005 13:32:40 +0100 (CET), Joolz
<joolz(at)arbodienst-limburg(dot)nl> wrote:
> Hello everyone,
>
> I'm building a postgresql db which will have to get lots of data
> from "the outside" (customers, that is). The db has lots of
> constraints, and I'm sure that our customers will offer lots of
> invalid information. We receive the information in csv format. My
> first thought was to read them into the database with COPY, but
> "COPY stops operation at the first error."
>
> What I need is an import where all valid lines from the csv files
> are read into the db, and I also get a logfile for all invalid
> lines, stating the line number plus the pg error message so I can
> see which constraint was violated.
>
> I can't think of a direct, elegant solution for this, does anyone
> have any suggestions? Thanks a lot!

How about a Perl script that uses DBI with autocommit turned on and
loops over the file using (something like) 'while (my @line =
split(',',scalar(<>)) {}'?

Perl tracks the line number of the current input file (including
STDIN) in $. (dollar-period). If you get a DBI error, 'warn "Input
error at line $.\n";'.

--
Mike Rylander
mrylander(at)gmail(dot)com
GPLS -- PINES Development
Database Developer
http://open-ils.org