PowerShell迁移MongoDB至PostgreSQL遇ERROR [22001]问题求助

核心问题定位 #

错误提示ERROR [22001] ERROR: value too long for type character varying(50)的根源并非_id字段，而是**Anleitung字段**：你的测试数据中Anleitung的内容是Lege eine Scheibe Brot auf das Brett und bestreiche es mit Butter，字符数远超建表时定义的VARCHAR(50)长度。你误以为是_id的问题，是因为错误提示没有明确指出具体字段，但实际触发超长限制的是Anleitung。

另外，你提到“除_id列外其他列均已成功加载”，大概率是因为单条测试数据的其他字段刚好没触发长度限制，但数组类型的Utensilien、Zutaten、Mengen直接插入会被转为System.Object[]字符串，后续也会出现数据格式异常或长度问题。

修复步骤 #

1. 调整表字段定义 #

修改建表语句，扩大Anleitung的长度限制（或直接用text类型），同时将数组类型字段改为PostgreSQL支持的数组类型：

CREATE TABLE Rezepte (
    id text PRIMARY KEY,
    Anleitung text,
    Name VARCHAR(20),
    Utensilien text[],
    Zutaten text[],
    Mengen text[]
);

如果表已存在，执行修改语句：

ALTER TABLE rezepte ALTER COLUMN Anleitung TYPE text;
ALTER TABLE rezepte ALTER COLUMN Utensilien TYPE text[];
ALTER TABLE rezepte ALTER COLUMN Zutaten TYPE text[];
ALTER TABLE rezepte ALTER COLUMN Mengen TYPE text[];

2. 修复插入逻辑（避免字符串拼接+处理数组） #

原脚本用字符串拼接生成SQL，不仅容易触发格式错误，还存在SQL注入风险，改用参数化查询，同时将PowerShell数组转为PostgreSQL兼容的数组格式：

# Insert data into the PostgreSQL table
try {
    $PgConn.Open()
    # 预定义参数化插入语句
    $insertQuery = @"
        INSERT INTO rezepte (id, Anleitung, Name, Utensilien, Zutaten, Mengen)
        VALUES (?, ?, ?, ?, ?, ?);
"@
    foreach ($Objekt in $Objekte) {
        # 将PowerShell数组转为PostgreSQL数组格式（{值1,值2}）
        $utensilien = "{" + ($Objekt.Utensilien -join ",") + "}"
        $zutaten = "{" + ($Objekt.Zutaten -join ",") + "}"
        $mengen = "{" + ($Objekt.Mengen -join ",") + "}"

        $command = $PgConn.CreateCommand()
        $command.CommandText = $insertQuery
        # 绑定参数
        $command.Parameters.AddWithValue("?", $Objekt._id.'$oid')
        $command.Parameters.AddWithValue("?", $Objekt.Anleitung)
        $command.Parameters.AddWithValue("?", $Objekt.Name)
        $command.Parameters.AddWithValue("?", $utensilien)
        $command.Parameters.AddWithValue("?", $zutaten)
        $command.Parameters.AddWithValue("?", $mengen)
        
        $null = $command.ExecuteNonQuery()
    }
    Write-Host "Data insertion successful."
} finally {
    $PgConn.Close()
}

额外注意事项 #

• 永远避免用字符串拼接生成SQL语句，参数化查询是规避格式错误和SQL注入的标准方案。
• MongoDB中的数组类型数据，在PostgreSQL中用对应数组类型存储，能更好保留数据结构，方便后续查询分析。
• 确保PostgreSQL数据库编码为UTF-8，避免特殊字符插入失败。

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