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".
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)
