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

Please, don't top-post on these lists.

On Tue, 2020-09-01 at 09:20 +0000, Junfeng Yang wrote:
>
> 发件人: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
> > On Tue, 2020-09-01 at 06:14 +0000, Junfeng Yang wrote:
> > > As described in the doc , 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
>
> 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?

I don't see the problem.

If you use the CSV format, your data will work fine the way they are:

COPY test FROM '/tmp/data' (FORMAT 'csv');

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