I would like to get the result of a query using rowMode="array" (as this is a potentially very large table and I don't want it formatted to object format) but I couldn't figure out how to pass in a array/list parameter for use in an IN operator.
const events = await t.manyOrNone({text: `select * from svc.events where user_id in ($1:list);`, rowMode: "array"}, [[1,2]]);
However, the above gives an error: syntax error at or near ":"
Removing the :list did not work either:
const events = await t.manyOrNone({text: `select * from svc.events where user_id in ($1);`, rowMode: "array"}, [[1,2]]);
Error: invalid input syntax for integer: "{"1","2"}"
I understand that this might be because I'm forced to use ParameterizedQuery format for rowMode="array" which does not allow those snazzy modifiers like :list, but this then leads to the question, if I were to use ParameterizedQuery format, then how do I natively pass in a Javascript array so that it is acceptable to the driver?
I guess an alternative formulation to this question is: how do I use arrays as parameters for ParameterizedQuery or PreparedStatements...
Answering my own question as I eventually found an answer to this issue: how to pass in arrays as params for use in the IN operator when using rowMode="array" | ParameterizedQuery | PreparedStatements.
Because this query is being parameterized in the server, we cannot use the IN operator, because the IN operator parameterize items using IN ($1, $2, $3...). Instead we need to use the ANY operator, where ANY($1) where for $1 an array is expected.
So the query that will work is:
const events = await t.manyOrNone({text: `select * from svc.events where user_id=ANY($1);`, rowMode: "array"}, [[1,2]]);