IN clause for multiple columns on the same value set

user_2434726 0
Viewed 41

I have a WHERE clause like below:

WHERE t1.col1 IN ('a', 'b', 'c') OR t2.col2 IN ('a', 'b', 'c');

I know that the two IN clauses will always have the same contents. Is there any way I can avoid duplicating them?

Something like:

WHERE (t1.col1 OR t2.col2) IN ('a', 'b', 'c')
postgresql
2 Answers

You can use the array overlaps operator:

where array[t1.col1, t2.col2] && array['a','b','c']
user_3303150 
with flt(x) as (values('{a,b,c}'::text[]))
select
...
from ..., flt
where t1.col1 = any(flt.x) or t2.col2 = any(flt.x);

There are two ways to use some constant values in the PostgreSQL. Using CTE as shown above. Or using "options":

set foo.bar to '{a,b,c}';
select '{c,d,e}'::text[] && current_setting('foo.bar')::text[];
user_5931440
Related