CODEWITHSUNDEEP
postgresql
Ice2burn
Ice2burn

Reputation: 691

PostgreSQL: How to convert comma separated string to work with IN operator

I was trying to make this query to work

select *
from DUALS
where num in ('1,2,3')

string '1,2,3' creating on the fly, so i cant replace it as list of integers in code.

So I end with this:

select *
from DUALS
where num in (select unnest (string_to_array('1,2', ',')::integer[]))

It works, but I'm wondering if it might be simplified. Can't use 'Any' operator.

Upvotes: 4

Views: 4479

Answers (2)

vernekap
vernekap

Reputation: 1

Alternate soluton is to use python string replacement - and then pass the full query string. Provided you know the dynamically generated comma separated string or can build it.

Python code: query = " select * from students where name in (%s);"

#some for loop which will create below string students_list_str = 'MUSK', 'TRUMP'

query_str = query%(students_list_str) #Now query_str = " select * from students where name in ('MUSK', 'TRUMP');"

cursor.execute(query_str)

Upvotes: 0

Diwas Poudel
Diwas Poudel

Reputation: 847

You can just use this code:

select *
from DUALS
where num = any('{1,2,3}'::int[])

Upvotes: 1

Related Questions