PostgreSQL使用COPY命令导出JSONB列格式异常如何解决

问题表现 #

表内jsonb类型字段payload原始存储格式正常：

payload
-------
{"test": "testing"}

执行带CSV参数的COPY导出命令：

sudo -u postgres psql test_database -c "COPY (SELECT payload FROM test_table) TO STDIN CSV"

输出内容被额外包裹外层引号，内部双引号被重复转义，不符合标准JSON格式，异常输出如下：

"{""test"":""testing""}"

问题原因 #

异常是CSV格式的标准转义规则导致的：CSV格式默认会将包含特殊字符（如双引号）的字段整体用双引号包裹，同时字段内部的双引号会被替换为两个连续双引号实现转义，该逻辑和JSON原生格式要求冲突。另外原命令中写TO STDIN属于笔误，导出到标准输出应使用STDOUT参数。

解决方法 #

• 方案1：直接使用COPY默认文本格式导出（推荐，适合导出纯JSON内容场景）
  去掉CSV参数，使用默认文本模式导出即可得到无额外转义的标准JSON内容，同时修正STDIN笔误：

  sudo -u postgres psql test_database -c "COPY (SELECT payload FROM test_table) TO STDOUT"
  

  该方式导出的每行内容对应一条原生jsonb数据，符合标准JSON格式，可直接被JSON解析工具读取。
• 方案2：必须保留CSV格式的场景，自定义转义规则避免JSON被转义
  如果业务要求必须输出CSV格式，可指定一个JSON内容中不会出现的字符作为引用符，避免双引号转义逻辑生效：

  sudo -u postgres psql test_database -c "COPY (SELECT payload FROM test_table) TO STDOUT CSV QUOTE E'\x01'"
  

  上述命令指定ASCII码0x01（标题开始字符，正常JSON内容不会包含）作为引用符，不会触发对JSON内双引号的转义处理。
• 方案3：导出为JSON Lines文件
  如果需要导出每行一个JSON对象的JSON Lines格式，直接使用方案1的默认文本模式即可，无需额外配置，导出结果完全符合JSON Lines规范。

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