PostgreSQL新手迁移SQL数据遇42601语法错误求解决

问题描述 #

我是PostgreSQL新手，正尝试从SQL Server迁移数据到PostgreSQL，执行代码时触发错误码'42601'的语法错误，定位到动态JavaScript赋值部分，但不清楚如何转为常规写法，附上创建AVProject表及插入数据的代码，恳请提供实现指引。

// Creating AVProject table
await pgClient.query(`DROP TABLE IF EXISTS "AVProject" CASCADE`)
await pgClient.query(`DROP SEQUENCE IF EXISTS "AVProject_ID_seq"`)
await pgClient.query(`
   CREATE TABLE "AVProject" (
       "ID" BIGSERIAL PRIMARY KEY NOT NULL,
       "AVProfessionalID" BIGINT NOT NULL, 
       "DeletedAt" TIMESTAMP NULL,
       "ProjectCode" TEXT NULL,
        "HoursCostCode" INT NULL,
        "ExpensesCostCode" INT NULL,
        "StartDate" TIMESTAMP NULL,
        "EndDate" TIMESTAMP NULL,
        "UnitType" TEXT NULL,
        "ExpensesReimbursed" INT NULL,
        "TravelDeductible" INT NULL,
        "ApproverID" BIGINT NULL,
        "CutoffPoint" TEXT NULL,
        "ProjectType" TEXT NOT NULL,
        "MagnoEntity" TEXT NULL,
        "TravelReimbursed" INT NULL,
        "ExpensesDeductible" INT NULL,
        "TravelExpensesDeductible" INT NULL,
        "PaidLeave" INT NULL,
        "Supplier" TEXT NULL,
        "MaxDeductibleTravel" NUMERIC NULL,
        "ClientCode" TEXT NULL,
        "ExactSupplierCode" TEXT NULL,
        "MIPProjectCode" TEXT NULL
   ) 
`)
await pgClient.query(`CREATE INDEX IF NOT EXISTS "idx_AVProject_ApproverID" ON "AVProject"("ApproverID");
`)

//Inserting AVProject
const rows_AVProject = await mssqlExec(
    mssqlConnection,

    `SELECT * FROM [MagnoIT].[dbo].[AVProject]`
)

let maxId_Project = 0

for (const project of rows_AVProject) {
    const deleted_at =
        project[2].value === null
            ? 'NULL'
            : `'${project[2].value.toISOString()}'`
    const start_date =
        project[6].value === null
            ? 'NULL'
            : `'${project[6].value.toISOString()}'`
    const end_date =
        project[7].value === null
            ? 'NULL'
            : `'${project[7].value.toISOString()}'`

    
    await pgClient.query(`INSERT INTO "AVProject" ( 
    "ID","AVProfessionalID", "DeletedAt", "ProjectCode", "HoursCostCode", "ExpensesCostCode", "StartDate", "EndDate",
    "UnitType", "ExpensesReimbursed", "TravelDeductible", "ApproverID", "CutoffPoint",
    "ProjectType", "MagnoEntity", "TravelReimbursed", "ExpensesDeductible", "TravelExpensesDeductible",
    "PaidLeave", "Supplier", "MaxDeductibleTravel", "ClientCode", "ExactSupplierCode",
    "MIPProjectCode") VALUES( 
   ${project[0].value}, ${project[1].value}, ${deleted_at}, '${
        project[3].value
    }', ${project[4].value}, ${
        project[5].value
    }, ${start_date}, ${end_date}, 
   '${project[8].value}', ${project[9].value ? 1 : 0}, ${
        project[10].value ? 1 : 0
    }, ${project[11].value}, '${project[12].value}',
    '${project[13].value}', '${project[14].value}', ${
        project[15].value ? 1 : 0
    }, ${project[16].value ? 1 : 0}, ${project[17].value ? 1 : 0}, 
    ${project[18].value ? 1 : 0}, '${project[19].value}', ${
        project[20].value
    },'${project[21].value}', '${project[22].value}', 
    '${project[23].value}')`)

    maxId_Project = project[0].value
}
await pgClient.query(
    `ALTER SEQUENCE "AVProject_ID_seq" RESTART WITH ${
        parseInt(maxId_Project) + 1
    }`
)

问题根源 #

当前代码通过字符串拼接生成INSERT语句，存在三个核心问题：

• 语法错误风险：当字段值包含单引号、特殊字符时，会直接破坏SQL语法结构，触发42601错误
• SQL注入风险：恶意数据可能篡改SQL逻辑
• 类型处理混乱：手动处理NULL、日期、布尔值容易出错

正确实现方案：使用参数化查询 #

PostgreSQL的Node.js客户端支持参数化查询，自动处理类型转换、特殊字符转义和NULL值，彻底避免上述问题。修改后的插入逻辑如下：

// Inserting AVProject
const rows_AVProject = await mssqlExec(
    mssqlConnection,
    `SELECT * FROM [MagnoIT].[dbo].[AVProject]`
)

let maxId_Project = 0
// 定义带参数占位符的INSERT模板
const insertQuery = `
INSERT INTO "AVProject" ( 
    "ID","AVProfessionalID", "DeletedAt", "ProjectCode", "HoursCostCode", "ExpensesCostCode", "StartDate", "EndDate",
    "UnitType", "ExpensesReimbursed", "TravelDeductible", "ApproverID", "CutoffPoint",
    "ProjectType", "MagnoEntity", "TravelReimbursed", "ExpensesDeductible", "TravelExpensesDeductible",
    "PaidLeave", "Supplier", "MaxDeductibleTravel", "ClientCode", "ExactSupplierCode",
    "MIPProjectCode"
) VALUES( 
    $1, $2, $3, $4, $5, $6, $7, $8, 
    $9, $10, $11, $12, $13,
    $14, $15, $16, $17, $18, 
    $19, $20, $21, $22, $23, 
    $24
)`

for (const project of rows_AVProject) {
    // 整理参数数组，直接传入原始值，客户端自动处理类型和NULL
    const params = [
        project[0].value,
        project[1].value,
        project[2].value ? new Date(project[2].value) : null,
        project[3].value,
        project[4].value,
        project[5].value,
        project[6].value ? new Date(project[6].value) : null,
        project[7].value ? new Date(project[7].value) : null,
        project[8].value,
        project[9].value ? 1 : 0,
        project[10].value ? 1 : 0,
        project[11].value,
        project[12].value,
        project[13].value,
        project[14].value,
        project[15].value ? 1 : 0,
        project[16].value ? 1 : 0,
        project[17].value ? 1 : 0,
        project[18].value ? 1 : 0,
        project[19].value,
        project[20].value,
        project[21].value,
        project[22].value,
        project[23].value
    ]
    // 执行参数化查询
    await pgClient.query(insertQuery, params)

    if (project[0].value > maxId_Project) {
        maxId_Project = project[0].value
    }
}

await pgClient.query(
    `ALTER SEQUENCE "AVProject_ID_seq" RESTART WITH ${parseInt(maxId_Project) + 1}`
)

额外优化建议 #

• 批量插入优化：如果数据量较大，建议使用批量插入（如INSERT ... VALUES ($1,...), ($2,...)）减少数据库请求次数，提升迁移速度
• 字段名映射：避免通过索引（project[0].value）访问字段，建议在SQL Server查询时指定字段名，通过字段名访问（如project.ID.value），代码可读性更强且不易出错
• 错误处理：添加try-catch块捕获迁移过程中的错误，便于定位问题

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