Proper syntax for "WHERE column IN [ArrayOfValues]"?

[jfeldstein]

I have an array of user ids, and would like to select all the users at once.

This query:

  this.db.query({
    text: "SELECT * FROM users WHERE remote_id IN $1 AND provider = $2",
    values: [array_of_ids, provider_string]
  }, cb);

Gives the error error: syntax error at or near "$1"

What's the correct syntax for this? Couldn't find it in the documentation or via google.

Thanks.

[chowey]

So far as I know, $1 doesn't work there. You would need to be a select statement or values statement.

The $1, $2, etc. syntax comes from prepared statements, which is actually how the pg module passes your query parameters. See the postgresql docs to understand how that works.

[chowey]

The way I have done it, which is a bit hackish, is something like this:

var values = [providor_string].concat(array_of_ids);
var params = array_of_ids.reduce(function (a, b, idx) {
  if (idx === 0) return '$' + (idx + 2);
  else return a + ', $' + (idx + 2);
}, '');
this.db.query({
  text: "SELECT * FROM users "
      + "WHERE remote_id IN (" + params + ") "
      + "AND provider = $1",
  values: values
}, cb);

[brianc]

Yeah, in support of what @chowey said...I believe the correct syntax for sending parameters in a "in list" query clause would be to match each parameter of the in-list to a different parameter placeholder. If google & other sites are failing to provide a defnintive answer, you could also ask on freenode in the #posgres chat room

[tiloso]

Thanks @chowey! Your solution works really well - just had to fix two minor bugs.
Line 3: replaced '>' in with '==='
Line 8: Remove 'VALUES' from the query text

[chowey]

Oops, sorry about that. I sort of just wrote the code without testing/debugging it first. I fixed my solution in case someone else gets tripped up by it.

[whitelynx]

Since node-postgres already correctly renders JavaScript Arrays as PostgreSQL array literals, there's a much simpler and less hackish solution possible; instead of using IN to test for membership, use = ANY(...):

client.query({
    text: "SELECT * FROM users"
            + " WHERE remote_id = ANY($1)"
            + " AND provider = $2",
    values: [
        [1, 3, 4], // remote_id values
        'Facebook', // provider
    ],
}, callback);

There is a full example in this gist: https://gist.github.com/whitelynx/5686162

I'm not sure what the performance considerations are of doing this instead of doing an actual IN clause; I haven't done any benchmarks on it. I wouldn't expect it to perform too differently, though.

It might be a good idea to add this to the answers for the FAQ question on this topic.

[brianc]

@whitelynx 👍

[jackniu81]

@whitelynx, perfect solution!

[ringerc]

@whitelynx I've updated the FAQ entry for this to reflect what you've said above. As I do not actually use node-postgres myself (I'm a PostgreSQL dev / Stack Overflow helper / PgJDBC contributor) I'd appreciate it if you could sanity-check the change and, if possible, add links to any node-postgres documentation that might exist to explain array parameters.