Re: error-tolerant COPY FROM

On Feb 4, 2005, at 8:27 AM, Joolz wrote:

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

Not to belabor the point, but using a trigger with a RAISED notice
gives the "context" in which the error is raised like:

NOTICE: Inserting a gene ID to meet foreign key requirements
CONTEXT: COPY g_ug, line 58245: "284462 Hs.128382"
NOTICE: Inserting a gene ID to meet foreign key requirements
CONTEXT: COPY g_ug, line 60235: "285936 Hs.174768"
NOTICE: Inserting a gene ID to meet foreign key requirements
CONTEXT: COPY g_ug, line 66339: "347813 Hs.406568"
NOTICE: Inserting a gene ID to meet foreign key requirements
CONTEXT: COPY g_ug, line 114352: "401610 Hs.454283"
NOTICE: Inserting a gene ID to meet foreign key requirements
CONTEXT: COPY g_ug, line 134275: "75875 Mm.300210"
NOTICE: Inserting a gene ID to meet foreign key requirements
CONTEXT: COPY g_ug, line 165798: "441201 Hs.535027"

Sean