Passing Array as Values in PG-Promise only gets one result

Question

I'm trying to pass an array of items to a SELECT query and get all the rows back with those items.

So far I've got it to work using a join method, but I'd prefer to just pass the array and understand what I'm doing wrong. Perhaps I should just use the join method?

This works:

var values = ['WH12345678','WH22345678']
values = "'" + values.join("','") + "'";
var query = "select * from pups where workorder IN (" + values + ")"
db.any(query)

I can't get this to work, tried tons of variations:

Note: It does current "work" in that it return the first item row, but not both.

var values = ['WH12345678','WH22345678']
var values = ["WH12345678","WH22345678"]
var query = "select * from pups WHERE workorder IN ($1:list)"
var query = "select * from pups WHERE workorder IN ($1:csv)"
db.any(query,values)

We cannot speculate why it returns just the first row, as you do not show the database methods you use for executing the queries. In any case, you should try to execute the final query directly, like via pgAdmin, and then compare it with results from `pg-promise` methods, and compare them. Then your question might have a meaning, when specifics are available. — vitaly-t, Jul 31 '23 at 14:10
@vitaly-t Is `db.any(query,values)` not the method call you're looking for? — Bergi, Jul 31 '23 at 20:56
You need to pass in `[values]`, not `values` to the query, so it is interpreted as a list. — vitaly-t, Aug 01 '23 at 06:50

score 2 · Accepted Answer · edited Aug 01 '23 at 18:44

From the docs of db.any:

the values can be:

a single value - to replace all $1 occurrences

an array of values - to replace all $1, $2, ... variables

an object - to apply Named Parameters formatting

Since you passed an array (['WH12345678','WH22345678']), the second case applies: $1 expands to 'WH12345678' and $2 (which you're not using in your query) would expand to 'WH22345678'.

You need to pass an array instead that has your values array as its first element and no further elements for other parameters:

var values = ["WH12345678","WH22345678"];
var query = "select * from pups WHERE workorder IN ($1:list)";
db.any(query, [values])
//            ^      ^

Alternatively, pass an object to use named parameters:

var values = ["WH12345678","WH22345678"];
var query = "select * from pups WHERE workorder IN (${values:list})";
//                                                    ^^^^^^
db.any(query, {values})
//            ^      ^

I have corrected the "named-parameter" example, to adhere to [Named Parameters](https://github.com/vitaly-t/pg-promise#named-parameters) syntax. — vitaly-t, Aug 01 '23 at 08:26
@vitaly-t oops, I didn't actually visit the link… thanks for the update! — Bergi, Aug 01 '23 at 08:58

Passing Array as Values in PG-Promise only gets one result

1 Answers1