Getting PostgresSQL 42703 error (invalid column error)-CodePudding

I am working on a React project with PostgreSQL database, this is the first time I am using it, and I am getting 42703 error on querying a particular column.

Below is the code I have written to query

const getList = (userId) => {

    return new Promise(function (resolve, reject) {
        pool.query(`SELECT items FROM public."user" where id=${userId}`, (error, results) => {
            if (error) {
                reject(error)
            }
            resolve(results);
        })
    })
}

I have defined this getList function and then I am making an api call to call this function by passing the userId like this

app.get(`/expenses`, verifySession(), async (req, res) => {
    const userId = req.session.userId;
    database.getList(userId)
        .then(response => {
            res.status(200).send(response);
        })
        .catch(error => {
            res.status(500).send(error);
        })
})

I even tried passing the userId directly as shown below , still it gives me the same error , which probably means I am querying in a wrong way

app.get(`/expenses`, verifySession(), async (req, res) => {
    //const userId = req.session.userId;
    database.getList('17a6dea6-a63e-4da7-9910-df7eddb672e6')
        .then(response => {
            res.status(200).send(response);
        })
        .catch(error => {
            res.status(500).send(error);
        })
})

Only when I directly write the string in the query it works properly like this

const getList = (userId) => {

    return new Promise(function (resolve, reject) {
        pool.query(`SELECT items FROM public."user" where id='17a6dea6-a63e-4da7-9910-df7eddb672e6'`, (error, results) => {
            if (error) {
                reject(error)
            }
            resolve(results);
        })
    })
}

Can someone please help we with what is exactly going wrong and if my syntax is correct or not ?

CodePudding user response：

This problem happened because you didn't use a single quotation for string type in the query. When using where id=${userId} and called with 17a6dea6-a63e-4da7-9910-df7eddb672e6 converted to where id=17a6dea6-a63e-4da7-9910-df7eddb672e6 and this make problem.

You can use two scenarios to handle it:

Use the single quotation for string type:

const getList = (userId) => {

    return new Promise(function (resolve, reject) {
        pool.query(`SELECT items FROM public."user" where id='${userId}'`, (error, results) => {
            if (error) {
                reject(error)
            }
            resolve(results);
        })
    })
}

Use parameter binding (As default it converted type)

const getList = (userId) => {

    return new Promise(function (resolve, reject) {
        pool.query(`SELECT items FROM public."user" where id=$1`, [userId], (error, results) => {
            if (error) {
                reject(error)
            }
            resolve(results);
        })
    })
}