unexpected data loaded into database when used COPY FROM

Started by jianggq@fujitsu.comalmost 5 years ago2 messageshackers
Jump to latest
#1jianggq@fujitsu.com
jianggq@fujitsu.com

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,

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: jianggq@fujitsu.com (#1)
Re: unexpected data loaded into database when used COPY FROM

"jianggq@fujitsu.com" <jianggq@fujitsu.com> writes:

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.

If what you're saying is that backslash-\r-\n results in the \r being
taken as a data character, there is exactly nothing unexpected about that.

regards, tom lane