PostgreSQL code '42601', error: syntax error at or near "s"

Question

I'm very new with PostgreSQL. I'm trying to migrate data from a SQL database to a PostgreSQL database. I figured it got something to do with a mix of plain and dynamic code, but I can't get further because I don't really understand it. I hope someone can give me some pointers on how I can correctly implement this. I had my piece of code like this:

     // 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
        }`
    )

The error is in my dynamic javascript syntax where I appoint the values of the data. but how do i get this in plain code?

Answer 1

The problem is you're just adding the values as strings in the middle of the query without any escaping. We can't even tell what the syntax error is, because it depends entirely on your data.

Instead, use a parameterised query!