PostgreSQL WHERE IN LIKE query

Question

I was wondering if it's possible to do a query using the IN clause where the options inside it are LIKE clauses, for example I have my existing SQL which returns the same results as I intend it just seems like a round about way to 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 with something along the lines of

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

Arsen · Accepted Answer · 2016-06-28 10:13:37Z

25

Use SIMILAR TO instead of LIKE

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

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

answered Jun 28, 2016 at 10:13

Arsen

11k3 gold badges39 silver badges47 bronze badges

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

3 Comments

TheLovelySausage Over a year ago

This answer solved my problem but if SIMILAR TO can do this with singular options and multiple options then why would someone ever use LIKE?

Arsen Over a year ago

@Trent SIMILAR TO uses regex under the hood. So I expect It may work slower than LIKE. But I don't think it's your case

Theodore R. Smith Over a year ago

This is awesome!!!! I can't believe I didn't find this out, say, in 2016... i had to wait 7 frickin years to find this gem!!

Abelisto · Accepted Answer · 2016-06-28 10:53:41Z

Actually using something IN (<value list>) is similar to something = any(array[<value list>]) in the 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 the additional advantage: it can be passed as a parameter from the client application.

Craig Ringer · Accepted Answer · 2016-06-28 10:56:52Z

6

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

The = can actually be replaced by any binary operator. Thus, you can use:

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

answered Jun 28, 2016 at 10:56

Craig Ringer

329k84 gold badges743 silver badges821 bronze badges

Comments

Anvita Shukla · Accepted Answer · 2021-02-03 15:51:06Z

2

Users of MySQL or Oracle may find it a bit different, but in PostGreSQL, to filter data using LIKE clause, one should use something like -

select * from table-name where column-name::text like '%whatever-to-be-searched%'

edited Feb 3, 2021 at 15:51

answered Feb 1, 2021 at 22:00

Anvita Shukla

4697 silver badges7 bronze badges

Comments

Raju Ahmed · Accepted Answer · 2023-11-21 06:32:24Z

0

"where name like any (array['%ab%', '%gh%', %xy%']);"

this is much faster then "SIMILAR TO".

answered Nov 21, 2023 at 6:32

Raju Ahmed

1652 silver badges10 bronze badges

Collectives™ on Stack Overflow

PostgreSQL WHERE IN LIKE query

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related