PostgreSQL中的WHERE IN LIKE查询

Question

我想知道是否可以使用IN子句进行查询，其中其内部的选项是LIKE子句，例如我有现有的SQL返回与我打算的相同结果，只是似乎这样做很繁琐。

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%'
)

有没有一种方法可以用类似的东西替换

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

- TheLovelySausage

Answer 1

使用SIMILAR TO替代LIKE

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

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

- Arsen

---

这个答案解决了我的问题，但如果 SIMILAR TO 可以同时处理单选和多选选项，那么为什么还会有人使用 LIKE？ - TheLovelySausage

@Trent SIMILAR TO 在底层使用正则表达式。因此，我预计它的工作速度可能比 LIKE 慢。但我不认为这是你的情况。 - Arsen

这太棒了！！！我简直不敢相信我没有在2016年就发现它......我必须等待7年才能找到这颗宝石！！ - Theodore R. Smith

Answer 2

在PostgreSQL中，使用 something IN (<value list>) 实际上类似于 something = any(array[<value list>])：

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)

幸运的是，我们可以使用like甚至ilike来代替=：

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

所以在你的情况下，可能会是这样

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

而且额外的好处是：它可以从客户端应用程序作为参数传递。

- Abelisto

Answer 3

x IN (a, b)可以看作是x = ANY(ARRAY[a,b])的简写。同样地，x IN (SELECT ...)和x = ANY (SELECT ...)也是如此。

=实际上可以被任何二元运算符替换。因此，您可以使用：

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

- Craig Ringer

Answer 4

使用MySQL或Oracle的用户可能会发现它们有些不同，但在PostGreSQL中，要使用LIKE子句过滤数据，应该使用类似以下的语句 -

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

- Anvita Shukla

Answer 5

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

这比"SIMILAR TO"要快得多。

- Raju Ahmed