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

[redirected from -hackers]
On Tue, 2020-09-01 at 06:14 +0000, Junfeng Yang wrote:
> 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?

Your problem is that the file contains bad data.

You are using the default TEXT format of copy, and backslashes must
be escaped there.

Everything will work as you want if you write the first line correctly like

122,as\\.d,adad

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com