Question 214066 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – node-postgres SELECT query succeeds when id column is targeted but fails when another column is targeted

Snapshot of the my stations table

When I select from the table by targeting the station_id column like this:

const getStationById = async ( id ) => {
  try {
    const station = await pool.query(`SELECT * FROM stations WHERE "station_id" = ${id}`)
    return station.rows[0]
  } catch (err) {
    return err
  }
}

It works normally.

But when I target another column like this:

const searchFinName = async (name) => {
 
  try {
    const stations = await pool.query(`SELECT station_id, fin_name, swe_name, address, city, operator, capacity, x_coordinate, y_coordinate FROM stations WHERE "fin_name" = ${name})
    return station.rows
  } catch (err) {
    return err
  }

}

it returns this error:

{"length":103,"name":"error","severity":"ERROR","code":"42703","position":"43","file":"parse_relation.c","line":"3666","routine":"errorMissingColumn"}.

I have not seen any other solution that helps, can someone please help me identify the problem, thanks.

Tags:

2

Answers


  1. 0

    There does not seem to be a problem with your logic, but rather with syntax or the amount of data being returned too large, which is not very likely.

    1. There is a missing closing quote "`" in your syntax above.

    2. Check that all column names passed in the query are correct.

    3. Try using the following query:

      SELECT * FROM stations WHERE ‘fin_name’ = ${name}

    As you are already projecting all the columns, using * would be wiser and can prevent the syntax fault that is most likely occurring.

    Login or Signup to reply.
  2. 0

    The problem here that postgres parse $name variable as a column name not as a string.

    So, the $name variable in your query should be surrounded by single quotes so that postgres can parse it as a string:

    const stations = await pool.query(`SELECT station_id, fin_name, swe_name, address, city, operator, capacity, x_coordinate, y_coordinate FROM stations 
WHERE "fin_name" = '${name}'`)

    Alternatively you can pass all the parameters in an array to the query function like this:

    const stations = await pool.query(`SELECT station_id, fin_name, swe_name, address, city, operator, capacity, x_coordinate, y_coordinate FROM stations 
WHERE "fin_name" = $1` , [name]);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search