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

Question

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

Clodoaldo Neto · Accepted Answer · 2017-09-13 09:22:49Z

6

= any:

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

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

answered Sep 13, 2017 at 9:22

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

1 Comment

HyopeR Over a year ago

He solved it from a different perspective. You're a savior. Cheers.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related