SELECT * FROM table WHERE id IN ($1)

[lanzz]

I could not figure how to approach the kind of query stated in the title. I have a dynamic list of values, and I need to use it in an IN operator in the query. Apparently node-postgres does not handle array parameters in such cases; also, I could not find any value escaping methods that would allow me to construct the query without using placeholders in this case. Constructing the query template and parameter list dynamically, adding as many placeholders as needed, seems unacceptably burdensome. What is the proper way to perform such a query?

[mattly]

node-postgres uses parameterized queries, so the actual text of the query string is what is passed to postgresql, $1 included, and the parameters are passed separately. Escaping is done by the postgresql server.

If you had an array of f.e. ['larry', 'curly', 'moe'] and wanted to search from the table 'stooges' on the column 'name', you would do this:

{ text: "SELECT * FROM stooges WHERE name IN ($1, $2, $3);"
, values: ['larry', 'curly', 'moe']
}

for queries where the IN value is of arbitrary length, you'll need to construct the "$1, $2, $3" string manually. There are several dozen ways to do this, none of which particularly efficient or elegant. I prefer coffeescript, and this is what I do:

inSet = ['larry', 'moe', 'curly']
inSetStr = ("$#{i}" for i in [1..inSet.length]).join(', ')

but if you're using straight javascript you might be well served by an enumeration utility library such as underscore.

[brianc]

@mattly thanks mattly. I need to write this up in the wiki...I think this is the 3rd issue on the subject.

[mattly]

@brianc of course; I'm just getting into using postgresql from node (as opposed to using it from ruby, or using redis/mongo from node) myself, and recently had to do some research into the issue. To be fair, there's not much documentation on using parameterized queries in general.

[lanzz]

Thanks, @mattly, though this is disappointing. @brianc, I take it that this would be the canonical solution?
This would make some queries even more unwieldy:

SELECT * FROM table WHERE col1 IN ($1, $2, $3) AND col2 IN ($4, $5, $6) OR col3 IN ($7, $8, $9)

as the generated lists of parameters would need to start at different indexes depending on the lengths of all the previous lists.

[booo]

Just for the record, this is also a valid request:

SELECT * FROM table WHERE col1 IN ($1, $2, $3) AND col2 IN ($1, $2, $3) OR col3 IN ($1, $2, $3)

Some people use a regex to create the indexes of the query. Use a special character in the text part of the query and replace it with ascending numbers.

[lanzz]

@booo I'm well aware of that, I do mean different values for each parameter in my example.

[brianc]

Yes, that's the canonical solution. Other databases have similar stories for doing IN clauses with parameters.

[charmander]

node-postgres supports arrays (now?), so:

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