Pass an array of integers in array of parameters

Question

I am trying to pass an array of parameters in pg-promise's array of parameters, as recommended in pg-promise docs.

db.any("SELECT fieldname FROM table WHERE fieldname = $1 AND fieldname2 IN ($2)",
        [1,[[1730442],[1695256]],[487413],[454336]]])
    .then(function (data) {
        console.log("DATA:", data); // print data;
    })
    .catch(); 

But it doesn't work, I'm returned a "missing ) after argument list" error. Or an "operator does not exist: integer = integer[]]" error, if I replace the parameters by :

[1,[1730442]]

Of course if I pass it like this, it works :

[1,1730442]

Is it the proper way of passing an array of values when other parameters are involved?

I also tried to remove the parenthesis around the $2, without success.

Answer 1

I am the author of pg-promise.

---

There is some confusion in your example...

You are using only two variables in the query, but passing in four values:

• 1
• [[1730442],[1695256]]
• [487413]
• [454336]

And your syntax there isn't a valid JavaScript, as you are using ] in the end without the matching opening one, so it is hard to understand what it is exactly you are trying to pass in.

And then why wrap all values in arrays again? I believe it is just a list of integers that you want inside the IN() statement.

When you want to use values within WHERE IN(), it is not really an array of those values that you want to pass in, it is a comma-separated list of values.

If you change your example to the following:

db.any('SELECT fieldname FROM table WHERE fieldname = $1 AND fieldname2 IN ($2:csv)',
[1, [1730442,1695256,487413,454336]])

You will get the correct list of values injected.

See also:

• CSV Filter
• WHERE col IN example.