How to pass an array of integers to sql IN() query

Question

Sadly, I somehow was unable to find an answer for this for the past two hours... I am trying to pass an array of integers into an SQL IN query like so:

"SELECT id, bio, image_url, userhandle FROM users IN ([4,6,7,8])"

Of course this is not gonna work so i tried this, but it only works with string arrays. formattedUserIds is the array of integers.

"SELECT id, bio, image_url, userhandle FROM users IN ('" + formattedUserIds.join(",") + "')"

to clarify, basically what i need is to convert [4,6,7,8] -> 4,6,7,8 so i can pass it into the in clause like IN(4,6,7,8) any help is appreciated.

You could pass-in a composite array parameter and `INNER JOIN` on it as an alternative to `WHERE IN`: https://stackoverflow.com/questions/47466801/table-valued-parameter-equivalent-in-postgresql — Dai, Feb 15 '21 at 03:03
I'm sorry, complete newbie at sql. How would i do this in my code? @Dai — frankied003, Feb 15 '21 at 03:28

score 2 · Answer 1 · answered Feb 15 '21 at 05:06

2

Use the ANY construct instead and pass the array as is:

SELECT id, bio, image_url, userhandle
FROM   users
WHERE  id = ANY($user_ids);

IN vs ANY operator in PostgreSQL

answered Feb 15 '21 at 05:06

Erwin Brandstetter

605,456
145
1,078
1,228

score 0 · Answer 2 · answered Feb 15 '21 at 05:05

Working on the example provided by Dai, here is a way you can get this done. Use the IN Clause as follows

SELECT id, bio, image_url, userhandle 
  FROM users IN (select *
                   from unnest(ARRAY[4,6,7,8])
                )

demo
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=27ca1628fb40540f9dcf07b8c4625e6e

score 0 · Answer 3 · edited Jul 12 '22 at 15:50

Define a function to parse array values to SQL return string

const parseArrayForSqlQuery = (stringArray: number[]) => {
  let returnString = '';
  stringArray.forEach((value, index) => {
    if (index > 0) {
      returnString = returnString + ',';
    }
    returnString = returnString + `${value}`; 
    // add an inner quotes if the values are string
  });
  return returnString;
}

Consider the below array should be pass to SQL statement

const arrayValues = [4,6,7,8];

Use the SQL statement like so

`SELECT id, bio, image_url, userhandle FROM users where users.id IN (${parseArrayForSqlQuery(arrayValues})`

Expected output

`SELECT id, bio, image_url, userhandle FROM users where users.id IN (4,6,7,8)`

score 0 · Answer 4 · answered Oct 24 '22 at 11:10

0

You can use find_in_set that can work with string array. e.g. 1,2,3,4

SELECT id, bio, image_url, userhandle
FROM   users
WHERE  find_in_set(users.id, $user_ids);

answered Oct 24 '22 at 11:10

Muhammad Tameem Rafay

3,602
2
21
32

score -1 · Answer 5 · answered Feb 15 '21 at 17:35

-1

I ended up just doing

  "SELECT id, bio, image_url, userhandle FROM users WHERE id IN (" + formattedUserIds.toString() + ")"

Thank you!

answered Feb 15 '21 at 17:35

frankied003

466
6
26

I downvoted this answer because of the risk of SQL injection (and because of the possibility that using variadic `IN` won't be able to take advantage of cached execution plans). What guarantees do you have that `formattedUserIds.toString()` will **always** return a syntactically correct substring? What if `formattedUserIds` is empty? If you're formatting strings using culture/locale-specific formatting then you might see commas as digit-grouping characters which will break your query. And so on and so on. – Dai Feb 17 '21 at 05:14

How to pass an array of integers to sql IN() query

5 Answers5