Re: COPY TO CSV produces data that is incompatible/unsafe for \COPY FROM CSV

Hello!

The documentation for COPY says "To avoid any misinterpretation, a \. data value appearing as a lone entry on a line is automatically quoted on output, and on input, if quoted, is not interpreted as the end-of-data marker".

The input part only seems to work when using the COPY FROM CSV command, not \COPY FROM CSV. This is mentioned in a previous message here https://www.postgresql.org/message-id/a89f47e1-f716-4ae3-b882-cab5032a5d66%40manitou-mail.org but not documented.

This means that COPY TO CSV produces data that \COPY FROM CSV cannot read, which I'm assuming should be fixed (or at the very least documented as a serious limitation of \COPY FROM CSV). I found this out by not being able to load a backup of a table that I had exported via COPY TO CSV.

As the above message also mentioned this can be a security risk if using \COPY FROM STDIN CSV with untrusted data (https://www.postgresql.org/message-id/20190128214448.GH26761%40momjian.us says "I think the question is how many people are using CSV/STDIN for insecure data loads?") but I would absolutely expect data produced with COPY TO CSV to be safe to pipe to a \COPY FROM CSV, but this bug makes that unsafe unless I also explicitly set ON_ERROR_STOP=1.

SQL to reproduce:

CREATE TABLE testtable (a TEXT);

INSERT INTO testtable VALUES ('

\.

');

COPY testtable TO '/run/postgresql/test.csv' CSV;

COPY testtable FROM '/run/postgresql/test.csv' CSV; -- This one works

\COPY testtable FROM '/run/postgresql/test.csv' CSV; -- This one does not work

Error message:

ERROR:  unterminated CSV quoted field

CONTEXT:  COPY testtable, line 1: ""

"

Versions tested:

psql (PostgreSQL) 14.3 (under arch linux)

psql (PostgreSQL) 13.7 (Ubuntu 13.7-0ubuntu0.21.10.1)