2

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

1 Answer 1

6

= any:

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

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

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

1 Comment

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.