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

Thanks, Laurenz!

I understand `\\.` should work. But this requires users to modify huge data.
I'm wondering is it possible to change the default end-of-data marker or
could we implement a method to let users choose their own marker?
________________________________
发件人: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
发送时间: 2020年9月1日 17:06
收件人: Junfeng Yang <yjerome(at)vmware(dot)com>; pgsql-general(at)postgresql(dot)org <pgsql-general(at)postgresql(dot)org>
主题: 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://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2Fcurrent%2Fsql-copy.html&amp;data=02%7C01%7Cyjerome%40vmware.com%7Cd75fda5803f54dcd9dc408d84e5640d7%7Cb39138ca3cee4b4aa4d6cd83d9dd62f0%7C0%7C0%7C637345479659888992&amp;sdata=CyY%2FAwPTB%2Bl3qMZbcxts5zKrW1QOiqL5%2Ft8MSEuaQk4%3D&amp;reserved=0, 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://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.cybertec-postgresql.com%2F&amp;data=02%7C01%7Cyjerome%40vmware.com%7Cd75fda5803f54dcd9dc408d84e5640d7%7Cb39138ca3cee4b4aa4d6cd83d9dd62f0%7C0%7C0%7C637345479659888992&amp;sdata=Q3lepnqcPJnJW9LPDCdXOyoejUf1mBsbovNWPMxhQ4g%3D&amp;reserved=0