Pre Exam Test Practice (SSC | Banking | IAS | GATE | State Level | CLAT | Railways | Insurance | Teaching Exams | NDA | Defence | IES |State Exam )

Organizational Research By

Surprising Reserch Topic

node postgres how to execute where col in dynamic value list query

node postgres how to execute where col in dynamic value list query  using -'node.js,node-postgres'

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.

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.

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.

asked Sep 18, 2015 by maurya
0 votes

Related Hot Questions

Government Jobs Opening