PostgreSQL WHERE IN LIKE query

Question

I was wondering if it is possible to make a query using the IN clause, where the parameters inside it are LIKE clauses, for example, I have my existing SQL that returns the same results as I assume it just looks like a round do it.

SELECT * FROM pg_stat_activity WHERE application_name NOT LIKE '%psql%' AND (current_timestamp - state_change) > INTERVAL '30 minutes' AND state IN ( SELECT state FROM pg_stat_activity WHERE state LIKE '%idle%' OR state LIKE '%disabled%' ) 

Is there a way to replace something line by line

 SELECT * FROM pg_stat_activity WHERE application_name NOT LIKE '%psql%' AND (current_timestamp - state_change) > INTERVAL '30 minutes' AND state IN ('%idle%', '%disabled%') 

Answer 1

Use SIMILAR TO instead of LIKE

AND state SIMILAR TO '%(idle|disabled)%'

https://www.postgresql.org/docs/9.0/static/functions-matching.html

Answer 2

Actually using something IN (<value list>) similar to something = any(array[<value list>]) in PostgreSQL:

 postgres=# explain select 1 where 'a' in ('a','b','c'); QUERY PLAN ---------------------------------------------------------- Result (cost=0.00..0.01 rows=1 width=0) One-Time Filter: ('a'::text = ANY ('{a,b,c}'::text[])) (2 rows) 

Fortunately, we can use like or even ilike instead of = :

 select 1 where 'aa' ilike any(array['%A%','%B%','%C%']); ?column? ---------- 1 (1 row) 

So in your case it could be

 ... state LIKE ANY(ARRAY['%idle%', '%disabled%']) 

And an additional advantage: it can be passed as a parameter from the client application.

Answer 3

x IN (a, b) can be shortened for x = ANY (ARRAY[a,b]) . Similarly, x IN (SELECT ...) and x = ANY (SELECT ...) .

= can actually be replaced by any binary operator. So you can use:

 SELECT ... WHERE x LIKE ANY (SELECT ...)