\copy produces CSV output that cannot be read by \copy

From: Nicolas Barbier <nicolas(dot)barbier(at)gmail(dot)com>
To: PostgreSQL Bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: \copy produces CSV output that cannot be read by \copy
Date: 2017-08-04 12:38:06
Message-ID: CAP-rdTbO35=U0+2Dn-i9FAYvau7Lm6Athd29KZ4sS+kDs21tLg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Igor Neyman 2017-08-04 13:23:26 Re: Replication to Postgres 10 on Windows is broken
Previous Message Daniel Verite 2017-08-04 09:55:28 Re: Crash report for some ICU-52 (debian8) COLLATE and work_mem values