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?

Hellfire

Have you tried converting the array to a string? Querys should get string objects to prepare the statement. – Madore

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

Gringo

Thank you for help, I take it i should not be getting error: syntax error at or near ":" and i have pg installed, do i need to add pg-promise aswell for this to work ? – Hellfire

@Hellfire pg is dated relative to pg-promise, and I see you're using promises anyway (await in your code), so yeah, instead of promisifyng pg, just switch over to pg-promise. After npm i just do const pg = require('pg-promise') and no changes in your app follow. – Gringo