Re: error-tolerant COPY FROM

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.