0

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.

frankied003
  • 406
  • 4
  • 12
  • 2
    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

3 Answers3

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);

Related:

Erwin Brandstetter
  • 539,169
  • 125
  • 977
  • 1,137
0

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
George Joseph
  • 5,424
  • 10
  • 22
-1

I ended up just doing

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

Thank you!

frankied003
  • 406
  • 4
  • 12
  • 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