PostgreSQL COPY命令处理转义分隔符报错问题求助

问题背景 #

• 用Java程序导入8GB左右的CSV到PostgreSQL，部分行包含转义逗号\,，逗号同时作为字段分隔符。
• MySQL使用对应命令可正常导入，但PostgreSQL触发报错：org.postgresql.util.PSQLException: ERROR: extra data after last expected column。

相关命令与代码 #

原PostgreSQL COPY命令 #

"COPY temporary_%s (%s) FROM STDIN with csv";

对比可用的MySQL命令 #

"LOAD DATA LOCAL INFILE ? "
          + "INTO TABLE temporary_%s "
          + "FIELDS TERMINATED BY ',' ENCLOSED BY '\"' "
          + "LINES TERMINATED BY '\n' "
          + "(%s)";

Java导入代码片段 #

BaseConnection physicalConnection =
    (BaseConnection) connection.unwrap(Class.forName("org.postgresql.core.BaseConnection"));

CopyManager copyManager = new CopyManager(physicalConnection);
FileReader fileReader = new FileReader(fileName);
copyManager.copyIn(String.format(PROCEDURE_POSTGRES, tableName, columns), fileReader);

问题数据示例 #

0.2\,4,000088797e02a729c72ff07bb9c5a84ef27e6b7c,1541592473000,1545585645000,0.3.0,1,NULL,NULL

期望第一列导入后为0.2,4。

完整报错信息 #

2025-02-04 12:00:40 2025-02-04 17:00:40.374 UTC [89725] STATEMENT:  COPY table_name (***, ***, ***, ***, ***, ***, ***, ***, ***) FROM STDIN with csv"
2025-02-04 12:03:48 2025-02-04 17:03:48.733 UTC [89890] ERROR:  extra data after last expected column
2025-02-04 12:03:48 2025-02-04 17:03:48.733 UTC [89890] CONTEXT:  COPY table_name, line 10: "  0.2\,4,000088797e02a729c72ff07bb9c5a84ef27e6b7c,1541592473000,1..."

解决方案 #

去掉csv选项，改用指定分隔符的普通文本模式，修改后的COPY命令如下：

"COPY temporary_%s (%s) FROM STDIN DELIMITER ','";

原因说明 #

PostgreSQL的COPY ... WITH CSV模式严格遵循RFC 4180标准，仅允许双引号包裹的字段内出现逗号，不支持反斜杠转义逗号。而MySQL的LOAD DATA默认支持反斜杠转义规则，因此能正确解析转义后的逗号。改用无csv选项的模式后，PostgreSQL会按指定分隔符处理数据，同时识别反斜杠为转义字符，从而将0.2\,4正确解析为单个字段的0.2,4。

内容的提问来源于stack exchange，提问作者b12