node-postgres: how to execute “WHERE col IN (<dynamic value list>)” query?

I'm trying to execute a query like this:

SELECT * FROM table WHERE id IN (1,2,3,4)


The problem is that the list of ids I want to filter against is not constant and needs to be different at every execution. I would also need to escape the ids, because they might come from untrusted sources, though I would actually escape anything that goes in a query regardless of the trustworthiness of the source.

node-postgres appears to work exclusively with bound parameters: client.query('SELECT * FROM table WHERE id = $1', [ id ]); this will work if I had a known number of values (client.query('SELECT * FROM table WHERE id IN ($1, $2, $3)', [ id1, id2, id3 ])), but will not work with an array directly: client.query('SELECT * FROM table WHERE id IN ($1)', [ arrayOfIds ]), as there does not seem to be any special handling of array parameters.

client.query('SELECT * FROM table WHERE id = $1', [ id ])

client.query('SELECT * FROM table WHERE id IN ($1, $2, $3)', [ id1, id2, id3 ])

client.query('SELECT * FROM table WHERE id IN ($1)', [ arrayOfIds ])

Building the query template dynamically according to the number of items in the array and expanding the ids array into the query parameters array (which in my actual case also contains other parameters besides the list of ids) seems unreasonably burdensome. Hard-coding the list of ids in the query template seems not viable either, as node-postgres does not provide any value escaping methods.

This seems like a very common use-case, so my guess is that I'm actually overlooking something, and not that it is not possible to use the common IN (values) SQL operator with node-postgres.

IN (values)

If anybody has solved this problem in a more elegant manner than those I listed above, or if I'm really missing something about node-postgres, please help.

2012-05-26 20:31:08 EEST ERROR: invalid input syntax for integer: "1,3" 2012-05-26 20:31:08 EEST STATEMENT: SELECT * FROM users WHERE id IN ($1)

5 Answers
5

We've seen this question before on the github issues list. The correct way is to dynamically generate your list of parameters based on the array. Something like this:

var arr = [1, 2, "hello"];
var params = ;
for(var i = 1; i <= arr.length; i++) {
params.push('$' + i);
}
var queryText = 'SELECT id FROM my_table WHERE something IN (' + params.join(',') + ')';
client.query(queryText, arr, function(err, cb) {
...
});


That way you get the postgres parameterized escaping.

client.query("SELECT * FROM stooges WHERE name = ANY ($1)", [ ['larry', 'curly', 'moe'] ], ...);

It looks like you may have been close based on your comment to @ebohlman's answer. You can use WHERE id = ANY($1::int). PostgreSQL will convert the array to the type the parameter is cast to in $1::int. So here's a contrived example that works for me:

WHERE id = ANY($1::int)

$1::int

var ids = [1,3,4];

var q = client.query('SELECT Id FROM MyTable WHERE Id = ANY($1::int)',[ids]);

q.on('row', function(row) {
console.log(row);
})

// outputs: { id: 1 }
// { id: 3 }
// { id: 4 }


arr

node-postgres

arr

arr

IN

The best solution I've found has been to use the ANY function with Postgres' array coercion. This lets you match a column with an arbitrary array of values as if you had written out col IN (v1, v2, v3). This is the approach in pero's answer but here I show that the performance of ANY is the same as IN.

ANY

col IN (v1, v2, v3)

ANY

IN

Your query should look like:

SELECT * FROM table WHERE id = ANY($1::int)


That bit at the end that says $1::int can be changed to match the type of your "id" column. For example, if the type of your IDs is uuid, you'd write $1::uuid to coerce the argument to an array of UUIDs. See here for the list of Postgres datatypes.

$1::int

uuid

$1::uuid

This is simpler than writing code to construct a query string and is safe against SQL injections.

With node-postgres, a complete JavaScript example looks like:

var pg = require('pg');

var client = new pg.Client('postgres://username:password@localhost/database');
client.connect(function(err) {
if (err) {
throw err;
}

var ids = [23, 65, 73, 99, 102];
client.query(
'SELECT * FROM table WHERE id = ANY($1::int)',
[ids], // array of query arguments
function(err, result) {
console.log(result.rows);
}
);
});


One of the best ways to understand the performance of a SQL query is to look at how the database processes it. The sample table has about 400 rows and a primary key called "id" of type text.

text

EXPLAIN SELECT * FROM tests WHERE id = ANY('{"test-a", "test-b"}');
EXPLAIN SELECT * FROM tests WHERE id IN ('test-a', 'test-b');


In both cases, Postgres reported the same query plan:

Bitmap Heap Scan on tests (cost=8.56..14.03 rows=2 width=79)
Recheck Cond: (id = ANY ('{test-a,test-b}'::text))
-> Bitmap Index Scan on tests_pkey (cost=0.00..8.56 rows=2 width=0)
Index Cond: (id = ANY ('{test-a,test-b}'::text))


You might see a different query plan depending on the size of your table, where there's an index, and your query. But for queries like the ones above, ANY and IN are processed the same way.

ANY

IN

Using pg-promise, this works well via the CSV Filter (comma-separated values):

const values = [1, 2, 3, 4];

db.any('SELECT * FROM table WHERE id IN ($1:csv)', [values])
.then(data => {
console.log(data);
})
.catch(error => {
console.log(error);
});


And to address the concern about various data types, :csv modifier serializes the array into csv, while converting all values into their proper PostgreSQL format, according to their JavaScript type, even supporting the Custom Type Formatting.

:csv

And if you have mixed-type values like this: const values = [1, 'two', null, true], you still will get the correctly escaped SQL:

const values = [1, 'two', null, true]

SELECT * FROM table WHERE id IN (1, 'two', null, true)


UPDATE

From v7.5.1, pg-promise started supporting :list as an interchangeable alias for the :csv filter:

:list

:csv

db.any('SELECT * FROM table WHERE id IN ($1:list)', [values])


Another possible solution is to use the UNNEST function like this:

UNNEST

var ids = [23, 65, 73, 99, 102];
var strs = ['bar', 'tar', 'far']
client.query(
'SELECT * FROM table WHERE id IN(SELECT(UNNEST($1))',
[ids], // array of query arguments
function(err, result) {
console.log(result.rows);
}
);
client.query(
'SELECT * FROM table WHERE id IN(SELECT(UNNEST($1))',
[strs], // array of query arguments
function(err, result) {
console.log(result.rows);
}
);


I've used this in a stored procedure and it works fine. Believe it should work also from node-pg code.

You can read about the UNNEST function here.

id = ANY($1)

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.