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?

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

Selfish · Accepted Answer · 2018-12-17 15:29:40Z

8

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

edited Dec 17, 2018 at 15:29

answered Dec 17, 2018 at 15:23

Selfish

6,2304 gold badges47 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Richlewis Over a year ago

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 ?

Selfish Over a year ago

@Richlewis 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.

Collectives™ on Stack Overflow

Pass array to function - PG and NodeJS

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related