查找文本数组包含类似于输入值的行 [英] Find rows where text array contains value similar to input

问题描述

我正在尝试获取其中 text [] 类型的列包含类似于某些用户输入的值的行。

I'm trying to get rows where a column of type text[] contains a value similar to some user input.

到目前为止，我一直在考虑和使用'ANY'和'Like 像这样的'运算符：

What I've thought and done so far is to use the 'ANY' and 'LIKE' operator like this:

select * from someTable where '%someInput%' LIKE ANY(someColum);

但这不起作用。该查询返回与该查询相同的值：

But it doesn't work. The query returns the same values as that this query:

select * from someTable where 'someInput' = ANY(someColum);

使用 unnest（）函数在子查询中，但如果可能，我需要在 WHERE 子句中对此进行查询。

I've got good a result using the unnest() function in a subquery but I need to query this in WHERE clause if possible.

为什么无法将 Like 运算符与 ANY 运算符一起使用，我什么也没得到错误？我认为原因之一应该是 ANY 运算符在查询的右边，但是...

Why doesn't the LIKE operator work with the ANY operator and I don't get any errors? I thought that one reason should be that ANY operator is in the right-hand of query, but ...

如果不使用 unnest（）并在 WHERE 子句中可行，是否有解决方案？

Is there any solution to this without using unnest() and if it is possible in WHERE clause?

推荐答案

理解 ANY 不是 运算符，但是只能用于运算符 right 的SQL构造。更多：

It's also important to understand that ANY is not an operator but an SQL construct that can only be used to the right of an operator. More:

• 如何在Rails的WHERE子句中使用ANY代替IN？

• How to use ANY instead of IN in a WHERE clause with Rails?

Like 运算符-或更准确地说： expression ，将其重写为< $ grec内部的code> ~~ 运算符-期望左边的 value 和 pattern 正确的。没有 COMMUTATOR （例如简单的等号运算符 = 一样），因此Postgres无法翻转操作数。

The LIKE operator - or more precisely: expression, that is rewritten with to the ~~ operator in Postgres internally - expects the value to the left and the pattern to the right. There is no COMMUTATOR for this operator (like there is for the simple equality operator =) so Postgres cannot flip operands around.

您的尝试：

select * from someTable where '%someInput%' LIKE ANY(someColum);

已左右翻转操作数，因此'％someInput％'是 value ，并且数组列 someColum 的元素被视为模式（这不是您想要的）。

has flipped left and right operand so '%someInput%' is the value and elements of the array column someColum are taken to be patterns (which is not what you want).

必须 ANY（someColum）像'％someInput％' -除非 ANY 构造不可能，该构造只允许运算符的 right 使用。您在这里遇到了障碍。

It would have to be ANY(someColum) LIKE '%someInput%' - except that's not possible with the ANY construct which is only allowed to the right of an operator. You are hitting a road block here.

相关：

• 有没有办法有用地索引包含正则表达式模式的文本列吗？


• PostgreSQL可以索引数组列吗？

• Is there a way to usefully index a text column containing regex patterns?
• Can PostgreSQL index array columns?

您可以规范化关系设计并保存数组的元素在单独表中的单独行中。除非 您已经发现了unnest（） 是解决方案。但是，尽管您仅对至少一个匹配元素的存在感兴趣，但 EXISTS 子查询将最有效，同时避免重复结果-Postgres可以尽快停止搜索找到第一个匹配项：

You can normalize your relational design and save elements of the array in separate rows in a separate table. Barring that, unnest() is the solution, as you already found yourself. But while you are only interested in the existence of at least one matching element, an EXISTS subquery will be most efficient while avoiding duplicates in the result - Postgres can stop the search as soon as the first match is found:

SELECT *
FROM   tbl
WHERE  EXISTS (
    SELECT -- can be empty 
    FROM   unnest(someColum) elem
    WHERE  elem LIKE '%someInput%'
  );

您可能想在 someInput 。请参阅：

• 用于正则表达式或LIKE模式的转义功能

• Escape function for regular expression or LIKE patterns

请谨慎使用否定（不喜欢全部（...）），当涉及到 NULL 时：

Careful with the negation (NOT LIKE ALL (...)) when NULL can be involved:

• 检查Postgres数组中是否存在NULL

• Check if NULL exists in Postgres array

这篇关于查找文本数组包含类似于输入值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！