How do I parameterize a Postgres array value in Node / pg-pool

Question

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?

Note that I got this working with a different kind of query: pool.query('SELECT * FROM songs WHERE $1 = ANY (style)', [style]);, but I'd like to know if there's a way to work with the form above. — rotarydial
– rotarydial, Commented Jul 30, 2020 at 6:12

jjanes · Accepted Answer · 2020-07-30 14:38:47Z

2

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]]);

answered Jul 30, 2020 at 14:38

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rotarydial Over a year ago

Both options you gave work, and make it clearer to me how to work with array columns. Thanks!

Collectives™ on Stack Overflow

How do I parameterize a Postgres array value in Node / pg-pool

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related