Pass array to function - PG and NodeJS

Question

Using pg in my NodeJS application I would like to be able to pass an array to a function so that I can use the IN statement as part of my query

Example

async function myMethod(array) {
  // array looks like [ '83', '82', '54', '55', '79' ] for example
  let response;
  try {
    response = await pool.query("SELECT * FROM fixtures WHERE id IN($1)", [array]);
  } catch (e) {
    console.log(e);
  }
  return response.rows;
}

I get an error

{ error: invalid input syntax for integer: "{"83","82","54","55","79"}"

How do I go about structuring my query here please?

Answer 1

Your best bet is to just go with:

pool.query("SELECT * FROM fixtures WHERE id IN($1:list)", [array]);
// [1,2,3]→"SELECT * FROM fixtures WHERE id IN(1,2,3)"

When a variable ends with :list, it is formatted as a list of Comma-Separated Values, with each value formatted according to its JavaScript type.

The reason you need that is that arrays are formatted to PostgreSQL arrays by default, which is denoted as you've seen with curly braces.

---

Note: This was added to pg (or rather, pg-promise), in v7.5.1. If you're using an earlier version, use $1:csv instead.

---

Source: pg-promise#csv-filter