This is a bug tracker
If you have a question, such has "how do you do X with Python/PostgreSQL/psycopg2" please write to the mailing list or open a question instead.
Please complete the following information:
OS: Linux Mageia Cauldron 10
Psycopg version: python3-psycopg2-2.9.6-2.mga10 (pachaged by Mageia)
Python version: 3.12.1
PostgreSQL version: PostgreSQL 15.5 on x86_64-mageia-linux-gnu, compiled by gcc (Mageia 12.3.0-3.mga9) 12.3.0, 64-bit
pip version pip 23.3.1 from /usr/lib/python3.12/site-packages/pip (python 3.12)
Describe the bug
Please let us know: wrong record number reported on COPY failure:
psycopg2.errors.BadCopyFileFormat: unterminated CSV quoted field
CONTEXT: COPY , line 2274912:
Interestingly, the file contains 2274912 records plus header. The actual record in error was 559346:
1: what you did: was importing file with over 2M records; failed with above error message. Errors occasionally occur in this data from an outside source.
2: what you expected to happen: report correct record number where failure occurred so I can fix it.
3: what happened instead, last data record in file was reported. Seems like when this error happens, psycopg2 flushes the rest of the file and reports the last record number rather than the record number with the error.
If possible, provide a script reproducing the issue: Bulk load like this; much private data redacted:
COPY TABLE ( ... ) FROM 'file.csv' WITH (FORMAT CSV, HEADER, DELIMITER ',', ENCODING 'ISO-8859-1');
File.csv:
[header]
[559344 records]
"D","0","B","A","B","R","U","U","F","4","9","D","N","2"," "," "," "," "," "," "," "," "," "," "," "," ","11/08/2022" # OK
"D","0","B","A","H","B","U","U","U","2","9","M","C","2"," "," "," "," "," "," "," "," "," "," "," "," ","11/08/2022" # OK
"D","0","B","A","K","J","W","N","F","53","2","D","N","2","H","","","","O, ","41"4","S"," "," ","","","","11/08/2022" # bad record # 559346
[remaining records ~1.7M+]
Error is at: "O, ","41"4","S"
This is a bug tracker If you have a question, such has "how do you do X with Python/PostgreSQL/psycopg2" please write to the mailing list or open a question instead.
Please complete the following information:
Describe the bug Please let us know: wrong record number reported on COPY failure: psycopg2.errors.BadCopyFileFormat: unterminated CSV quoted field CONTEXT: COPY, line 2274912:
Interestingly, the file contains 2274912 records plus header. The actual record in error was 559346:
1: what you did: was importing file with over 2M records; failed with above error message. Errors occasionally occur in this data from an outside source. 2: what you expected to happen: report correct record number where failure occurred so I can fix it. 3: what happened instead, last data record in file was reported. Seems like when this error happens, psycopg2 flushes the rest of the file and reports the last record number rather than the record number with the error.
If possible, provide a script reproducing the issue: Bulk load like this; much private data redacted: COPY TABLE ( ... ) FROM 'file.csv' WITH (FORMAT CSV, HEADER, DELIMITER ',', ENCODING 'ISO-8859-1');
File.csv: [header] [559344 records] "D","0","B","A","B","R","U","U","F","4","9","D","N","2"," "," "," "," "," "," "," "," "," "," "," "," ","11/08/2022" # OK "D","0","B","A","H","B","U","U","U","2","9","M","C","2"," "," "," "," "," "," "," "," "," "," "," "," ","11/08/2022" # OK "D","0","B","A","K","J","W","N","F","53","2","D","N","2","H","","","","O, ","41"4","S"," "," ","","","","11/08/2022" # bad record # 559346 [remaining records ~1.7M+] Error is at: "O, ","41"4","S"