error-tolerant COPY FROM

[Joolz]

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!


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Michael Glaesemann]

On Feb 4, 2005, at 21:32, Joolz wrote:

> 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!

I don't know if it's elegant or not, but my approach to this situation
is to read the data into a temp table that does not have as strict
constraints. Once the data is in the database, I process it further,
moving the data that's valid into the appropriate table. Then I see
what's left, and what further processing I need to do to "fix" the
invalid data. Perhaps a similar strategy would work for you.

Michael Glaesemann
grzm myrealbox com


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [email]majordomo@postgresql.org[/email] so that your
message can get through to the mailing list cleanly

[Joolz]

Michael Glaesemann zei:

>
> On Feb 4, 2005, at 21:32, Joolz wrote:
>

>> 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!

>
> I don't know if it's elegant or not, but my approach to this
> situation
> is to read the data into a temp table that does not have as strict
> constraints. Once the data is in the database, I process it further,
> moving the data that's valid into the appropriate table. Then I see
> what's left, and what further processing I need to do to "fix" the
> invalid data. Perhaps a similar strategy would work for you.

I'm afraid this is a bit too indirect IMHO. As I want to know the
line number in which an error occurs, I would have to traverse the
error-tolerant table with limit 1 offset N, and report N when an
error occurs, hoping that the row order is identical to the line
order in the csv file.


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Joolz]

Csaba Nagy zei:

> [snip]

>> I'm afraid this is a bit too indirect IMHO. As I want to know the
>> line number in which an error occurs, I would have to traverse the
>> error-tolerant table with limit 1 offset N, and report N when an
>> error occurs, hoping that the row order is identical to the line
>> order in the csv file.

>
> So why don't you insert the line number too in the error tolerant
> table
> when you do the import ? Then you will have the line number all the
> time. Not that I would understand what will you do with that line
> number, once you already have all the data in the table...

I need the line number to let the customer know which data were
refused, and the corresponding errormessage to let him know why.


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match