CODEWITHSUNDEEP
sqlarrayspostgresql
Oleh Rybalchenko
Oleh Rybalchenko

Reputation: 8039

How to bind array parameter for filtering with IN operator in Postgres

I need to bind parameter that contains list of values and then use it in the query for filtering with IN operator. Assume we have following schema:

create table test_table (
    id  serial primary key,
    channel text
);

insert into test_table(channel) values ('FOO'), ('BAR'), ('BAZ');

Basically the query acts like:

select * from test_table
where channel in ('FOO', 'BAR');

But I need to pass the channels array dynamically. I've tried:

select * from test_table
where channel in (string_to_array('FOO, BAR', ',')::text[]);

ERROR: operator does not exist: text = text[] Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 42

So my question is: how to use string_to_array for IN operator? Or how to bind array parameter to the query and use it for IN?

SQLFiddle

Upvotes: 2

Views: 2686

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

= any:

where channel = any (string_to_array('FOO, BAR', ',')::text[]);

https://www.postgresql.org/docs/current/static/functions-comparisons.html#AEN21108

Upvotes: 6

Related Questions