Re: Is it possible to set end-of-data marker for COPY statement. - Mailing list pgsql-hackers

From Bruce Momjian
Subject Re: Is it possible to set end-of-data marker for COPY statement.
Date
Msg-id 20200901160502.GW13613@momjian.us
Whole thread Raw
In response to Is it possible to set end-of-data marker for COPY statement.  (Junfeng Yang <yjerome@vmware.com>)
Responses Re: Is it possible to set end-of-data marker for COPY statement.
Re: Is it possible to set end-of-data marker for COPY statement.
List pgsql-hackers
On Tue, Sep  1, 2020 at 06:14:45AM +0000, Junfeng Yang wrote:
> Hi hackers,
> 
> As described in the doc https://www.postgresql.org/docs/current/sql-copy.html,
> the TEXT format recognizes
> backslash-period (\.) as end-of-data marker.
> 
> The example below will raise an error for the line contains `\.`.
> 
> CREATE TABLE test (
> id int,
> name text,
> dep text
> )
> 
> Data in file "/tmp/data".
> 
> 122,as\.d,adad
> 133,sa dad,adadad
> 
> Then execute
> 
> copy test from '/tmp/data' DELIMITER ',';
> 
> An end-of-copy marker corrupt error will be raised.
> 
> This requires users to escape the end-of-data marker manually in their data.
> Why we don't have a mechanism to define other characters as end-of-data marker?
> Or there are other ways to avoid escape the end-of-data in data?

This is the first I am hearing of this.  The problem is that the system
can't decide if \. is escaping a delimiter, or the end-of-copy marker. 
I think we need to just disable period as a delimiter.  I don't think
there is enough demand to allow the end-of-data marker to be
configurable.

Interestingly, you can use period as s delimiter if you are copying from
a file that doesn't need an end-of-data marker and you never need to
escape the delimiter, but that seems like too rare a use case to allow
period to be supported as a delimiter.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee




pgsql-hackers by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: "cert" + clientcert=verify-ca in pg_hba.conf?
Next
From: Dave Page
Date:
Subject: Re: Kerberos support broken on MSVC builds for Windows x64?