Description: If the end-of-data marker \. occurs on a line of its own in a multiline string value, \copy outputs it to CSV without any quoting. Subsequently reading that CSV file using \copy fails.
Version:
$ psql
psql (9.5.7)
Type "help" for help.
test=# select version();
version
---------------------------------------------------------------------------------------------------
PostgreSQL 9.5.7 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-16) 6.3.0 20170425, 64-bit
(1 row)
How to reproduce:
CREATE TABLE test (t varchar);
INSERT INTO test (t) VALUES ('
\.
');
\copy (SELECT t FROM test) TO 'test.csv' WITH CSV;
\copy test FROM 'test.csv' WITH (FORMAT CSV);
This results in:
ERROR: unterminated CSV quoted field
CONTEXT: COPY test, line 1: ""
"
Expected result: \copy can read the files it produced itself.
Analysis: I think that neither the writing nor the reading is incompatible with the documentation. Rather, there doesn’t seem to be a way to write such values to CSV that will be read back correctly (or at least I couldn‘t find such a way), as the reader always stops when encountering \. on a line by itself, and because there doesn’t seem to be a way to escape such a value in a way that wouldn’t produce a \. on a line by itself.
Nicolas
