Line number in error message is incorrect when loading a CSV-file with multi-line records.

From: Patrick van Coeverden <patrick(dot)van(dot)coeverden(at)topicus(dot)nl>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: Line number in error message is incorrect when loading a CSV-file with multi-line records.
Date: 2017-09-06 18:24:58
Message-ID: 08f86723-0672-eecb-900a-930960d7e6ec@topicus.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hello,

I am importing CSV files into a Postgres database with the COPY statement.
Some of the CSV files do have records that span over multiple lines.
These records are surrounded by double-quotes.

This imports fine when there are no errors.

However, when an error is thrown because of an unique constraint, the
line number in the error message is off.

I have attached an example CSV and SQL file.

When executing the attached SQL-file (change the location of the CSV),
the following error message appears:
ERROR: duplicate key value violates unique constraint "import_test_pkey"
DETAIL: Key (id)=(3) already exists.
CONTEXT: COPY import_test, line 13

However, the error in the CSV is located at line 23 (Or the 4th record)

Postgres version: "PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by
gcc (Debian 4.7.2-5) 4.7.2, 64-bit"
OS: Linux Mint

Thanks,

Patrick van Coeverden

Attachment Content-Type Size
csv_with_multiline_records.csv text/csv 447 bytes
line_nr_test.sql application/sql 392 bytes

Browse pgsql-bugs by date

  From Date Subject
Next Message Andres Freund 2017-09-06 19:23:53 Re: [BUGS] Bug in Physical Replication Slots (at least 9.5)?
Previous Message David G. Johnston 2017-09-06 18:07:32 Re: BUG #14800: substring produces different results with similar types