4

Running NodeJS and pg-promise.

I've got an array:

let my_array = new array('x', 'y', 'z');

I'm trying to pass this as a parameter to my PostgreSQL query with pg-promise:

db_webhooks.any('SELECT cars FROM my_cars WHERE id IN ($1)', [my_array])
  .then...

But it fails with type error. I've tried to .join the array, but also get error, as it result in a string 'x,y,z' instead of 'x','y','z'.

Printing out the debug/query called gives me:

QUERY: SELECT cars FROM my_cars WHERE id IN (array['x','y','z'])

This should be:

QUERY: SELECT cars FROM my_cars WHERE id IN ('x','y','z')
Alfred Balle
  • 1,135
  • 4
  • 16
  • 32
  • `[my_array]` will create an array with one element, namely your array `new array('x', 'y', 'z')`. Try just `db_webhooks.any(... , my_array)` – k0pernikus Jul 02 '18 at 10:46
  • That gives me `QUERY: SELECT cars FROM my_cars WHERE id IN ('x')`, so only the first elements is included. – Alfred Balle Jul 02 '18 at 10:48
  • 1
    Seems like `... id IN ($1:csv)` solves the issue. – Alfred Balle Jul 02 '18 at 10:55
  • Then it appears that your call does not accept an array parameter but wants a comma-separated string, and then `my_array.join(",")` may do the trick. – k0pernikus Jul 02 '18 at 10:58
  • And if you found a solution to your own problem, please don't hesitate to post your own answer. – k0pernikus Jul 02 '18 at 10:59
  • See these posts: https://stackoverflow.com/questions/10720420/node-postgres-how-to-execute-where-col-in-dynamic-value-list-query/ and https://stackoverflow.com/questions/50381949/where-col-in-with-named-parameters – vitaly-t Jul 02 '18 at 13:04
  • @AlfredBalle Why did you never accept my answer? – vitaly-t Aug 03 '21 at 12:55

1 Answers1

5

The library supports CSV Filter for this purpose:

await db.any('SELECT cars FROM my_cars WHERE id IN ($1:csv)', [my_array])
/*=> SELECT cars FROM my_cars WHERE id IN ('x', 'y', 'z') */

or you can use :list, which is the same:

await db.any('SELECT cars FROM my_cars WHERE id IN ($1:list)', [my_array])
/*=> SELECT cars FROM my_cars WHERE id IN ('x', 'y', 'z') */
vitaly-t
  • 24,279
  • 15
  • 116
  • 138