bind message supplies 2 parameters, but prepared statement "" requires 1

Question

• I am using pg-promise to execute the following query

Here is the raw query variable

const query = `SELECT * FROM feed_items WHERE feed_item_id=$1 AND '{$2}' <@ tags`

• tags is an array and I want to check if item is present in the array
• I keep getting this error bind message supplies 2 parameters, but prepared statement "" requires 1 despite supplying 2 values Can someone please suggest where I am going wrong

Answer 1

Prepared Statements won't let you do it, because it is too limited. But pg-promise native formatting is quite flexible, and you can do it in several ways...

• Via ':value' filter, you can use either '{$2#}' or {$2:value}

• Via Custom Type Formatting, you can use $2 directly, while wrapping the value into the following:

const wrap = a => ({rawType: true, toPostgres: () => pgp.as.format('{$1#}', [a])});

or like this:

const wrap = a => ({rawType: true, toPostgres: () => pgp.as.format('{$1:value}', [a])});

or even like this:

const wrap = a => ({rawType: true, toPostgres: () => `'{${pgp.as.value(a)}}'`});

example:*

await db.any('SELECT ... $1 <@ tags', [wrap(123)]);
//=> SELECT ... '{123}' <@tags'