PostgreSQL WHERE IN LIKE 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38073291/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL WHERE IN LIKE query
提问by TheLovelySausage
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.
我想知道是否可以使用 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%'
)
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%')
采纳答案by Arsen
Use SIMILAR TOinstead of LIKE
使用SIMILAR TO而不是LIKE
AND state SIMILAR TO '%(idle|disabled)%'
AND state SIMILAR TO '%(idle|disabled)%'
https://www.postgresql.org/docs/9.0/static/functions-matching.html
https://www.postgresql.org/docs/9.0/static/functions-matching.html
回答by Abelisto
Actually using something IN (<value list>)is similar to something = any(array[<value list>])in the PostgreSQL:
实际使用something IN (<value list>)类似于something = any(array[<value list>])在 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 likeor even ilikeinstead of =:
幸运的是，我们可以使用like甚至ilike代替=：
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.
还有一个额外的好处：它可以作为参数从客户端应用程序传递。
回答by Craig Ringer
x IN (a, b)can be consisidered shorthand for x = ANY (ARRAY[a,b]). Similarly, x IN (SELECT ...)and x = ANY (SELECT ...).
x IN (a, b)可以被认为是 的简写x = ANY (ARRAY[a,b])。同样，x IN (SELECT ...)和x = ANY (SELECT ...)。
The =can actually be replaced by any binary operator. Thus, you can use:
该=实际上可通过任何二进制运算符来代替。因此，您可以使用：
SELECT ... WHERE x LIKE ANY (SELECT ...)
