mysql - 从 MySQL csv 导出的 PostgreSQL csv 导入？

Question

它会起作用吗？

MySQL 导出:

SELECT * INTO OUTFILE 'C:/data.csv'
FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM table;

PostgreSQL:

COPY table FROM 'C:/data.csv' WITH DELIMITER AS '\t' NULL AS '\\N' CSV

由于某种原因缺少列。所以我相信定界符有问题。我是对的，我该怎么办？我可以在下面检查导致错误的行。但是我必须寻找哪些字符？

ERROR:  missing data for column "Column21"
CONTEXT:  COPY table, line 88219: ...

Answer 1

1. 正如@knitti 提到的，postgres 需要知道转义字符:ESCAPE '\'
2. OPTIONALLY ENCLOSED BY '"' 是 csv 的错误格式。最好强制引用。

完整代码:

MySQL 5.5.15:

SELECT *
INTO OUTFILE 'C:/data.csv'
FIELDS TERMINATED BY '\t' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM table;

postgres 9.0.3:

COPY table FROM 'C:/data.csv' WITH DELIMITER AS '\t' NULL AS '\\N' ESCAPE E'\\' CSV