Search code examples
javascriptpg-promise

Filter CSV from pg-promise takes only the first value from array

My pg-promise query (2 ids in array passed in):

db.any(`SELECT * FROM users WHERE user_id IN ($1:csv)`, team.member_ids)

Console output of query and params passed to pg-promise:

SELECT * FROM users WHERE user_id IN ($1:csv) [ 1, 2 ]
//Fails one row returned, two expected

However I get only one result. If I run the following query in psql I get two results:

select * from users where user_id IN (1,2);
//Works successfully with two rows returned

Any help is appreciated!

Solution

  • This is explained in Query Formatting, how one value versus array changes the values interpretation:

    This however works only for types number, bigint, string, boolean, Date and null, because types like Array and Object change the way parameters are interpreted.

    db.any(`SELECT * FROM users WHERE user_id IN ($1:csv)`, team.member_ids)
//=> SELECT * FROM users WHERE user_id IN (1)

    Because above you are passing in the value-array itself directly, $1 refers to the first element in that array. To make $1 refer to the entire value-array, that value itself needs to be inside an array:

    db.any(`SELECT * FROM users WHERE user_id IN ($1:csv)`, [team.member_ids])
//=> SELECT * FROM users WHERE user_id IN (1,2)

    Alternatively, the recommended use of Named Parameters makes the code more concise:

    db.any(`SELECT * FROM users WHERE user_id IN ($<member_ids:csv>)`, team)
//=> SELECT * FROM users WHERE user_id IN (1,2)

    In general, use pg-monitor to see what queries are being executed.