Question 220132 in PostgreSQL Question posted in
The official documentation can be found here.

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

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)
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I beleive that values in PG-Promise is not meant to be an array that runs the query multiple times and that would be inefficient regardless. Its more meant to contain an object and be able to write queries and access properties by name. I could possibly pass the obj below and try to access array by property name but I found using the join method worked fine for now.

    During my testing, I also noted that sending an array as application/json caused issues as well as by text as it tried to treat it as a string.

    My end result was to send an object with one value like so:

    var obj = {"array": ['WH12345678','WH22345678']}

    And my query like so:

    var values = req.body
values = values['array']
values = "'" + values.join("','") + "'";
var query = "select * from pups where workorder IN (" + values + ")"

    Note: It was important NOT to use " in my array, but ' and it helped me to run as I did in OP via "hard coded" SQL then translate into pg-promise/js


  2. 0

    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});
//            ^      ^
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search