Date: Sun, 14 Aug 2022 00:00:53 -0700
From: Noah Misch <noah@leadboat.com>
To: Svante Richter <pgsql-bugs@richter.id>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: COPY TO CSV produces data that is incompatible/unsafe for \COPY
 FROM CSV
Message-ID: <20220814070053.GA92855@rfd.leadboat.com>
References: <bfcd57e4-8f23-4c3e-a5db-2571d09208e2@beta.fastmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <bfcd57e4-8f23-4c3e-a5db-2571d09208e2@beta.fastmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Archived-At: 
 <https://www.postgresql.org/message-id/20220814070053.GA92855%40rfd.leadboat.com>
Precedence: bulk

On Wed, Jun 15, 2022 at 02:16:14PM +0200, Svante Richter wrote:
> 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.

I agree this warrants a doc mention.  Also, the previous thread seems to have
ended with a focus on [\copy ... from stdin], but it's broader than that:

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

The psql documentation says [\copy ... from stdin] looks for "\.".  It says
nothing about doing that for [\copy ... from filename].  I wonder if psql
should change the filename case to send the whole file to the server, ignoring
"\." inside.  (That is to say, change to match the psql documentation.)  This,
too, has an avoidable case of the problem:

  create table t (c text);
  \copy t from program 'printf ''"foo\n\\.\nbar"''' with csv