JavaScript (Postgres DB) - How to use a prepared statement with an array as parameter in the WHERE IN ( ) clause

Question

I am currently using the database class from http://vitaly-t.github.io/pg-promise/Database.html and trying to implement an Update statement using a PreparedStatment on my Postgres DB while having an Array passed to the WHERE IN clause ?

const updatePreparedStatment = new PS('prepared-statement', 'UPDATE mytable SET "MESSAGE"=$1 WHERE "ID" IN ($2)', ["dummy update", ["1","2","3"]]);

Diannadianne

Asking your question well increases the likelihood that you'll get help. You should post your code here, in your question, rather than posting a link to an external page. A sample of the table(s) you're querying would also be helpful. Guidelines on asking question well can be found here: stackoverflow.com/help/how-to-ask – Bristletail

By very definition of Prepared Statements, variable formatting in them occurs on the server-side, which means you cannot make use of the powerful query-formatting engine that's inside pg-promise, limiting yourself to just basic $1, $2,... variables supported by PostgreSQL itself, and so you'd have to format that WHERE IN part all by yourself. You should ask yourself first, whether you really need Prepared Statements to begin with. – Precautionary

Answer 1

It is described in the FAQ of node-postgres https://github.com/brianc/node-postgres/wiki/FAQ#11-how-do-i-build-a-where-foo-in--query-to-find-rows-matching-an-array-of-values

How do I build a WHERE foo IN (...) query to find rows matching an array of values? node-postgres supports mapping simple JavaScript arrays to PostgreSQL arrays, so in most cases you can just pass it like any other parameter.

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

Note that = ANY is another way to write IN (...), but unlike IN (...) it will work how you'd expect when you pass an array as a query parameter.

If you know the length of the array in advance you can flatten it to an IN list:

// passing a flat array of values will work:
client.query("SELECT * FROM stooges WHERE name IN ($1, $2, $3)", ['larry', 'curly', 'moe'], ...);

... but there's little benefit when = ANY works with a JavaScript array.

If you're on an old version of node-postgres or you need to create more complex PostgreSQL arrays (arrays of composite types, etc) that node-postgres isn't coping with, you can generate an array literal with dynamic SQL, but be extremely careful of SQL injection when doing this. The following approach is safe because it generates a query string with query parameters and a flattened parameter list, so you're still using the driver's support for parameterised queries ("prepared statements") to protect against SQL injection:

var stooge_names = ['larry', 'curly', 'moe'];
var offset = 1;
var placeholders = stooge_names.map(function(name,i) { 
    return '$'+(i+offset); 
}).join(',');
client.query("SELECT * FROM stooges WHERE name IN ("+placeholders+")", stooge_names, ...);

Hope that helps since google fails to find this

Emotional