unexpected data loaded into database when used COPY FROM

From: "jianggq(at)fujitsu(dot)com" <jianggq(at)fujitsu(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: unexpected data loaded into database when used COPY FROM
Date: 2021-07-07 07:07:43
Message-ID: OS0PR01MB5972BD2A88058A716D9FBAE9AA1A9@OS0PR01MB5972.jpnprd01.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi

When I used COPY FROM command on windows, I found that If the line data ends with a backslash and carriage return/newlines(\r\n),COPY FROM mishandle the line .
As a result, there were unexpected data loaded into database.

The following case can reproduce this issue.
----------------------------------------------------------------------------
Data file:
lines ending with carriage return/newlines(\r\n)
----- test.txt ------
AAA\ ★there is only one Backslash characters (\) in the line end.
BBB
-------------------

Data loading:
#CREATE TABLE copytest( a TEXT);
#COPY copytest FROM '/test.txt';

Data in database:
# SELECT * FROM copytest;
a
-------
aaa\r ★\r is loaded unexpectedly
bbb
(2 rows)
--------------------------------------------------------------------------

In this case , is it better to throw an error to user than to load the unexpected data to database?

Regards,

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tatsuo Ishii 2021-07-07 07:11:23 Re: [HACKERS] WIP aPatch: Pgbench Serialization and deadlock errors
Previous Message Jakub Wartak 2021-07-07 06:53:51 RE: Cosmic ray hits integerset