MongoDB转PostgreSQL时COPY命令导入数组类型CSV数据报错问题

问题分析 #

MongoDB导出的CSV中数组字段格式为"[""val1"",""val2""]"，PostgreSQL无法识别该格式；手动改为PostgreSQL数组格式{val1,val2}后，又因数组内的逗号被CSV解析为字段分隔符，导致extra data after last expected column错误。

解决方案 #

方案1：修改CSV数组字段格式并添加引号包裹 #

PostgreSQL的数组格式为{val1,val2}，但CSV中包含逗号的字段必须用双引号包裹，避免被误判为字段分隔符。

批量修改方法（Notepad++）

使用正则替换给所有数组字段添加双引号：

• 查找正则：\{(.*?)\}
• 替换为："{\1}"

修改后CSV的数组字段示例：

2024-09-24T05:50:00.114Z,2025-03-11T21:00:00.000Z,121,"{TRT120325T12,TRT120325T20}",121T2,85.640,4

执行原COPY命令即可正常导入。

方案2：导出时直接生成兼容PostgreSQL的CSV #

用mongosh编写脚本替代mongoexport，在导出阶段处理数组格式并做好CSV转义：

const uri = process.env.URI;
const authDb = process.env.AUTH_DB;
const dbName = process.env.DB_NAME;
const collName = process.env.COLLECTION_NAME;
const outputFile = process.env.OUTPUT_FILE;

const { MongoClient } = require('mongodb');
const fs = require('fs');

async function exportToCSV() {
  const client = new MongoClient(uri, { authSource: authDb });
  await client.connect();
  const db = client.db(dbName);
  const cursor = db.collection(collName).find();

  const fields = ['last_update', 'matDate', 'section', 'isins', 'CBRTCode', 'presVal', 'payRate'];
  const writer = fs.createWriteStream(outputFile);
  writer.write(fields.join(',') + '\n');

  await cursor.forEach(doc => {
    // 将MongoDB数组转为PostgreSQL格式，并用双引号包裹
    const isinsStr = `"{"${doc.isins.join('","')}"}"`;
    
    // 处理字段转义，避免CSV解析错误
    const row = [
      doc.last_update || '',
      doc.matDate || '',
      doc.section || '',
      isinsStr,
      doc.CBRTCode || '',
      doc.presVal || '',
      doc.payRate || ''
    ].map(val => {
      if (typeof val === 'string' && (val.includes(',') || val.includes('"'))) {
        return `"${val.replace(/"/g, '""')}"`;
      }
      return val;
    }).join(',');
    
    writer.write(row + '\n');
  });

  await writer.end();
  await client.close();
}

exportToCSV().catch(console.error);

执行脚本：

mongosh --nodb export-script.js

导出的CSV可直接用原COPY命令导入。

方案3：通过临时表中转导入 #

无需修改原始CSV，先导入到临时表，再通过SQL转换数组格式：

1. 创建临时表：

CREATE TEMP TABLE temp_sgmk_hazine_tanim (
    last_update text,
    matDate text,
    section text,
    isins text,
    CBRTCode text,
    presVal text,
    payRate text
);

2. 导入CSV到临时表：

COPY temp_sgmk_hazine_tanim FROM '/home/srvadmin/docker/build/aktarimlar/sgmk_hazine_tanim.csv' DELIMITER ',' CSV HEADER;

3. 转换数据到正式表：

INSERT INTO sgmk_hazine_tanim (last_update, matDate, section, isins, CBRTCode, presVal, payRate)
SELECT
    to_timestamp(last_update, 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"') AS last_update,
    to_date(matDate, 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"') AS matDate,
    section::smallint,
    -- 解析MongoDB格式的数组为PostgreSQL数组
    string_to_array(regexp_replace(isins, '^\["(.*)"\]$', '\1'), '","') AS isins,
    CBRTCode,
    presVal,
    payRate::numeric(8,4)
FROM temp_sgmk_hazine_tanim;

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