如何在执行 psql 复制命令时保留换行符

Question

我的csv文件中有以下内容(有3列(：

141413,""'/x=/></script></title><x><x/","Mountain View, CA"'/x=/></script></title><x><x/"
148443,"CLICK LINK BELOW TO ENTER^^^^^^^^^^^^^^","model

xxx lipsum as it is

100 sometimes unknown

travel evening market
"

当我使用以下命令在mysql中导入上面提到的csv时，它将backslash((视为换行符;这是预期的行为。

LOAD DATA INFILE '1.csv' INTO TABLE users FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY 'n';

MYSQL 输出

但是当我尝试使用 copy 命令导入到 psql 时，它将 \ 视为普通字符。

copy users from '1.csv' WITH (FORMAT csv, DELIMITER ',', ENCODING 'utf8', NULL "N", QUOTE E'"', ESCAPE '');

后置输出

Answer 1

在导入 CSV 文件之前尝试解析这些，例如使用perl -pe或sed以及来自psql的STDIN：

$ cat 1.csv | perl -pe 's/\n/n/g' | psql testdb -c "COPY users FROM STDIN WITH (FORMAT csv, DELIMITER ',', ENCODING 'utf8', NULL "N", QUOTE E'"', ESCAPE '');"

这是导入后的样子：

testdb=# select * from users;
id   |                 company                 |                    location                     
--------+-----------------------------------------+-------------------------------------------------
141413 | "'/x=/></script></title><x><x/          | Mountain View, CA"'/x=/></script></title><x><x/
148443 | CLICK LINK BELOW TO ENTER^^^^^^^^^^^^^^ | model                                          +
|                                         |                                                +
|                                         | xxx lipsum as it is                            +
|                                         |                                                +
|                                         | 100 sometimes unknown                          +
|                                         |                                                +
|                                         | travel evening market                          +
|                                         | 
(2 Zeilen)