postgresql query fails in node.js but succeeds in pgadmin

Question

I have the following postgresql query:

SELECT user_id, username, full_name, avatar, verified FROM users WHERE user_id IN (1,2,3)

It should select all users where the ID is 1, 2 or 3. ID type is bigint. In pgadmin, this query works like it should, but using node-postgres, I get the following error:

error: invalid input syntax for type bigint: "1,2,3"

Node.js code example:

// I have an array user_ids_array containing all user ID's
// I convert it to a string using join()
const user_ids = user_ids_array.join(',')

query = "SELECT user_id, username, full_name, avatar, verified FROM users WHERE user_id IN ($1)"

values = [user_ids]

result = await pool.query(query, values)

I guess it has something to do with user_id (field) being an integer (bigint) and user_ids being a string, but I don't know how to typecast it.

I found this question but I also gives as solution using WHERE some_id IN (1,2).

Thanks in advance!

Answer 1

One way to solve this problem is doing a unnest in your array.

Given your current code your arrayids, must be a default array in javascript (no cast to string):

const arrayids = [1,2,3]

const sql = "SELECT user_id, username, full_name, avatar, verified FROM users WHERE user_id IN (SELECT UNNEST($1::int[]))"

result = await pool.query(query, [arrayids])

Notice that the second parameter from pool.query is different from your current use case.

Hope it helps.

In my project I used the lib pg. Not sure if it will work with node-postgres.