使用PGAdmin4导入含jsonb与数组的CSV时遭遇JSON语法错误

导入CSV时触发以下错误：

ERROR:  invalid input syntax for type json
DETAIL:  Token "size" is invalid.
CONTEXT:  JSON data, line 1: {size...
COPY media, line 1, column details: "{size: 1097267, width: 2950, height: 2094, dateTime: null, latitude: null, longitude: null, cameraMo..."

表结构 #

ID (int) name(text) details(jsonb) tags(text[])

数据示例（制表符分隔） #

27   Beautiful beach {"size": 1097267, "width": 2950}    {amazing,asia,attraction}

导入设置 #

• 分隔符：制表符（Tab）
• 引号字符：双引号
• 未勾选「Header」选项
• 编码：UTF8

问题原因 #

PostgreSQL的JSON/JSONB类型要求严格遵循标准JSON格式，其中键必须用双引号包裹。但CSV里的JSON内容（如{size: 1097267, width: 2950}）未给键添加双引号，属于非标准JSON，导致解析失败。

解决方法 #

方法1：修改CSV文件（推荐） #

将CSV中JSON部分的所有键用双引号包裹，转换成标准JSON格式：

27   Beautiful beach {"size": 1097267, "width": 2950}    {amazing,asia,attraction}

修改后直接用PGAdmin的导入工具即可正常导入。

方法2：使用COPY命令配合正则转换（适用于无法修改CSV的场景） #

执行COPY命令时，先将details列按文本读取，再通过正则表达式给键添加双引号，最后转换为JSONB类型：

COPY media(id, name, details, tags)
FROM '/path/to/your/media.csv'
DELIMITER E'\t'
QUOTE '"'
ENCODING 'UTF8'
AS (id int, name text, details_text text, tags text[])
SET details = jsonb(regexp_replace(details_text, '(\w+):', '"\1":', 'g'));

注意：该正则替换仅适用于键为字母/数字/下划线的简单场景，如果JSON包含特殊字符（如空格、连字符），需要调整正则或优先选择方法1。

内容的提问来源于stack exchange，提问作者Muhammad Arslan Jamshaid