PostgreSQL csv从MySQL csv导出导入吗？ [英] PostgreSQL csv import from a MySQL csv export?

问题描述

它将起作用吗？

MySQL导出：

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

PostgreSQL：

PostgreSQL:

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

由于某些原因缺少列，因此我认为定界符存在问题，我正确吗，我该怎么办？我可以检查带有以下原因错误的行但是，我必须寻找哪些字符？

There is missing columns for some reason. So I believe there is problem in delimiter. Am I correct, what can I do? I can inspect row with cause error below. But which characters I must look for?

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

推荐答案

1. 如@knitti所说，postgres需要知道转义字符：ESCAPE'\'


2. 对于，CSV的格式不正确。最好强制使用引号。

完整代码：

mysql 5.5。 15：

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：

postgres 9.0.3:

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

这篇关于PostgreSQL csv从MySQL csv导出导入吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！