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.

Detour

In your example you are not passing in an array of integers as a parameter, it is an array of arrays of integers. Which one do you actually want? – Alfonse

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.

Alfonse

Thanks for the thorough answer. The third ] is only because I was passing both an integer and an array of integers. I didn't notice the ($2:csv) notation in the docs though. – Detour

@Stanislasdrg it is in the WHERE col IN example and in the format API ;) – Alfonse

Just tested, it works. Thank you again for your help and for this wonderful library. – Detour

Answer 2

Another possibility is:

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

From postgresql manual here: https://www.postgresql.org/docs/9.5/static/functions-comparisons.html

The right-hand side is a parenthesized expression, which must yield an array value. The left-hand expression is evaluated and compared to each element of the array using the given operator, which must yield a Boolean result. The result of ANY is "true" if any true result is obtained. The result is "false" if no true result is found (including the case where the array has zero elements).

If the array expression yields a null array, the result of ANY will be null. If the left-hand expression yields null, the result of ANY is ordinarily null (though a non-strict comparison operator could possibly yield a different result). Also, if the right-hand array contains any null elements and no true comparison result is obtained, the result of ANY will be null, not false (again, assuming a strict comparison operator). This is in accordance with SQL's normal rules for Boolean combinations of null values.

SOME is a synonym for ANY.

Peipeiffer

I feel like you just bumped my SQL skills up a level. I'm curious though if you have any experience with performance gotcha's with this method? Do you think that this is generally more performant - because it can become a prepared statement - or just on par with the IN form? Other? – Beaudry

Answered my own question - it's the same as of 8.2: In existing releases, the form with IN (list-of-scalar-constants) can be optimized into indexscan(s), but = ANY (array) isn't. 8.2 will treat them equivalently (in fact, it converts IN (...) to = ANY (ARRAY[...]) !). So depending on your time horizon, you might wish to stick with whichever is cleaner for your calling code. regards, tom lane From postgresql.org/message-id/5373.1158351561%40sss.pgh.pa.us – Beaudry