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?

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?

• javascript
• arrays
• node.js
• pg

• Have you tried converting the array to a string? Querys should get string objects to prepare the statement. – Guim Commented Dec 17, 2018 at 15:18

1 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