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

Question

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.

I think is simple enough. But if you want can create a function to receive string a return integer[] — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented May 16, 2017 at 14:09
Because of cross-platfrom reason. Some DB engines can use ANY predicates only with subqueries, but works fine with in '1,2,3'. — Ice2burn
– Ice2burn, Commented May 16, 2017 at 15:12
If this is supposed to be portable, how can you use unnest and string_to_array or array types in general? — Laurenz Albe
– Laurenz Albe, Commented May 16, 2017 at 15:25
preprocessor for postgresql in this case. Ugly but works. The way of less resistance for now. In general, original query is portable, but not in postgresql. In postgresql it will look like second query. — Ice2burn
– Ice2burn, Commented May 16, 2017 at 15:29

Diwas Poudel · Accepted Answer · 2020-05-01 11:18:13Z

1

You can just use this code:

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

answered May 1, 2020 at 11:18

Diwas Poudel

8591 gold badge11 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ice2burn Over a year ago

>> Can't use 'Any' operator.

vernekap · Accepted Answer · 2024-12-24 10:32:35Z

0

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)

answered Dec 24, 2024 at 10:32

vernekap

11 bronze badge

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related