postgresql - 使用带有转义字符的 ilike any()

Question

在 PostgreSQL 中，您可以使用 ILIKE 执行不区分大小写的查询:

select * from test where value ilike 'half is 50$%' escape '$'

并且您可以通过将 ILIKE 与 ANY() 结合使用来一次查询多个值

select * from test where value ilike any(array['half is 50%', 'fifth is 20%'])

上面的查询将匹配 'Fifth is 2019'，这是我不想要的，但是当我尝试使用 ILIKE 和 ANY() 带有转义字符，我收到语法错误。

我是不是遗漏了什么愚蠢的东西，或者这根本不受支持？如果没有，是否有另一种方法以不区分大小写的方式同时查询多个值？

编辑:澄清一下，查询将通过 JDBC 接受参数，因此实际的 SQL 看起来像

select * from test where value ilike any(?) escape '$'

这就是为什么我希望将用户输入的 % 和 _ 解释为文字。

Answer 1

ILIKE 中的ESCAPE 子句仅指文字，不适用于表达式。你应该使用反斜杠，或者如果不可能，你可以尝试:

with test(value) as (
values 
    ('half is 50%'),
    ('half is 50x'),
    ('fifth is 20%'),
    ('fifth is 2000')
)

select * 
from test
where value ilike any(select replace(unnest(array['half is 50$%', 'fifth is 20$%']), '$', '\'))

    value     
--------------
 half is 50%
 fifth is 20%
(2 rows)

看起来有点笨拙，但效果很好。