如何生成可通过PostgreSQL COPY命令加载、包含带双引号和反斜杠的varchar数组的CSV文件

我太懂你这种头疼的感觉了——用PostgreSQL的COPY命令导入CSV时，碰到带特殊字符的varchar数组，要么直接报错，要么导入后数据缺胳膊少腿，转义规则绕来绕去的确实容易搞混。结合你用的PostgreSQL 13版本，我给你理清楚正确的处理逻辑：

核心逻辑：兼顾CSV和PostgreSQL数组的双重转义规则 #

你之前踩坑的根源在于，没同时照顾到CSV格式的转义要求和PostgreSQL数组字面量的转义要求，两者的规则不一样，必须配合起来用：

1. CSV层面：如果字段内容里包含逗号、双引号这类特殊字符，整个字段必须用双引号包裹；字段内部的双引号要转义成两个连续的双引号（这是CSV的标准转义方式，PostgreSQL COPY默认遵循这个）。
2. 数组层面：数组元素里的双引号必须用反斜杠\转义，反斜杠本身也要转义成两个连续的反斜杠\\（这是PostgreSQL解析数组字面量的规则）。

针对你的例子的正确写法 #

我们逐个处理你给出的三个元素，最终生成符合要求的CSV字段内容：

• 原元素"quote" → 数组里要写成\"quote\"（数组转义双引号）
• 原元素x1"x2 → 数组里要写成x1\"x2（数组转义双引号）
• 原元素C:\Users → 数组里要写成C:\\Users（数组转义反斜杠）

把这些组合成完整的数组字面量：{"\"quote\"","x1\"x2","C:\\Users"}

现在把这个数组字面量作为CSV的一个字段，按照CSV规则转义：

• 用双引号包裹整个字段
• 把字段内的每一个双引号都替换成两个双引号

最终CSV里的字段内容就是：
"{"""\"quote\"""","""x1\"x2""","""C:\\Users"""}"

完整CSV示例 #

假设你的表有id和array_col两列，CSV文件内容可以写成这样：

id,array_col
1,"{"""\"quote\"""","""x1\"x2""","""C:\\Users"""}"

执行你的COPY命令：

COPY table FROM filename WITH (FORMAT csv, HEADER);

导入后查询，你会得到完全符合预期的数组值：{"\"quote\"","x1\"x2","C:\\Users"}，对应原始的三个元素。

说说你之前尝试的问题 #

• 第一种尝试{"""quote""","x1""x2","C:\Users"}：没有用双引号包裹整个字段，CSV解析时会把数组里的逗号当成列分隔符，所以报错“extra data after last expected column”。
• 第二种尝试"{"""quote""","x1""x2","C:\Users"}"：只做了CSV层面的双引号转义，没处理数组层面的转义——PostgreSQL解析数组时会把连续双引号合并，反斜杠会被当成转义符吃掉，所以最终数据丢失了引号和反斜杠。
• 第三种尝试"{"\""quote\""","x1\""x2","C:\\Users"}"：转义逻辑混乱，CSV和数组的转义规则没配合好，导致部分字符解析错误。

备注：内容来源于stack exchange，提问作者augustomen