parameterized query with an IN operator

[thewizarodofoz]

Is it possible to use a parameterized query with an IN operator?

I'm imagining something like:

const text = 'SELECT * FROM tbl WHERE col1 IN ($1) AND col2 = $2';
const values = [[1, 2, 3], 1000000];

Currently I'm injecting the values for IN using a string template:

const text = `SELECT * FROM tbl WHERE col1 IN (${[1, 2, 3].join(', ')}) AND col2 = $1`;
const values = [1000000];

[charmander]

Use an array parameter. (#82 (comment), #129 (comment), #623)

const text = 'SELECT * FROM tbl WHERE col1 = ANY ($1) AND col2 = $2';
const values = [[1, 2, 3], 1000000];

[matt212]

@charmander
well below is not working

const text = 'SELECT * FROM employees WHERE lower(a."first_name") IN ($1)   AND lower(a."gender") IN ($2)';
const values = [['aamer','babette'], ['f']];

what would be correct way to use above

[brianc]

where col = any($1) use that when using an array as a param.

…

On Fri, Aug 26, 2022 at 10:49 AM matt212 ***@***.***> wrote: @charmander <https://github.com/charmander> well below is not working const text = 'SELECT * FROM employees WHERE lower(a."first_name") IN ($1) AND lower(a."gender") IN ($2)'; const values = [['aamer','babette'], ['f']]; what would be correct way to use above — Reply to this email directly, view it on GitHub <#1452 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAAMHIKKV5JSPWSDLIGF5Y3V3DRSDANCNFSM4D25EWPA> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[matt212]

thanks @brianc
so my final where clause looks like this

and lower(a."gender") = ANY ($1) and lower(a."first_name") = ANY($2)
cool

[brianc]

perfecto!

[brianc]

yup!

…

On Fri, Aug 26, 2022 at 11:10 AM matt212 ***@***.***> wrote: thanks @brianc <https://github.com/brianc> so my final where clause looks like this and lower(a."gender") = ANY ($1) and lower(a."first_name") = ANY($2) cool — Reply to this email directly, view it on GitHub <#1452 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAAMHIPP4ACT6MDCFY6OLUTV3DT5TANCNFSM4D25EWPA> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[rockie]

How about NOT IN?

[sehrope]

For the reverse you can use ALL(...) in the same manner:

SELECT *
FROM some_table t
WHERE t.id != ALL($1::int[])

(Though watch out with indexing on that kind of thing as it'll probably be a full scan.)

Good to keep in mind that:

• With = you almost always want to use = ANY(...)
• With != you almost always want to use != ALL(...)

Otherwise if there's more than one value in the list of values, the negated operators don't really make sense. For example, x != ANY('{1,2,3}'::int[]) will always be true because if the value matches any one item of the array, it definitely does not match the rest. Similarly, x = ALL('{1,2,3}'::int[]) will always be false because a value cannot equal more than one thing.

[evgeny-kim]

Using pg-format:

import pgFormat from 'pg-format';

const query = pgFormat(`select ... where field in %L`, values[]);
const result = await pool.query(query);