I am querying an array column (style text[]) using the contains operator @>. My raw query is:
SELECT * FROM songs WHERE style @> '{Acoustic}'
When I drop this into node (using pg-pool) and attempt to parameterize it, the braces surrounding the value reference seem to prevent it from working properly. Note these examples:
const style = 'Acoustic';
// this works, but I don't want to in-line like this:
const { rows } = await pool.query(`SELECT * FROM songs WHERE style @> '{` + style + `}'`);
// this fails with 'error: bind message supplies 1 parameters, but prepared statement "" requires 0'
const { rows } = await pool.query(`SELECT * FROM songs WHERE style @> '{$1}'`, [style]);
What's the proper way to parameterize this?
const { rows } = await pool.query(`SELECT * FROM songs WHERE style @> '{$1}'`, [style]);
The $1 here, being in quotes, is just regarded as part of the string, not as a substitutable variable. If you wish to pass in a scalar and have it treated as an array, you should be able to do it this way:
const { rows } = await pool.query(`SELECT * FROM songs WHERE style @> ARRAY[$1]`, [style]);
An alternative might be to have node.js bind an array directly (not tested):
const { rows } = await pool.query(`SELECT * FROM songs WHERE style @> $1`, [[style]]);