Home How to bind array parameter for filtering with IN operator in Postgres
Reply: 1

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

Oleg Rybalchenko
1#
Oleg Rybalchenko Published in 2017-09-13 09:15:45Z

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
2#
Clodoaldo Neto Reply to 2017-09-13 09:22:49Z

= any:

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

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

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.302374 second(s) , Gzip On .

© 2016 Powered by cudou.com design MATCHINFO