了解数组的ILIKE ANY元素-Postgresql [英] Understanding ILIKE ANY element of an array - postgresql

问题描述

我必须选择表中的所有行（将其命名为 mytable ），将其指定列中的值（将其命名为 mycolumn ）不等于 A，不等于 S。

I have to select all the lines in a table (let's call it mytable) for which the value in a given column (let's call it mycolumn) is not equal to 'A' and not equal to 'S'.

所以我尝试了类似的事情

So I tried something like

SELECT * FROM mytable WHERE mycolumn NOT ILIKE ANY(ARRAY['A','S'])

我更喜欢使用 ILIKE 而不是使用 = 来测试字符串是否相等，因为值 A和 S在我的数据中可能是小写的，所以我希望值

I prefer the use of ILIKE instead of the use of = to test string equalities because the values 'A' and 'S' may come in lower-case in my data, so I want the values 's' and 'a' to be excluded as well.

足够奇怪的是，上面的查询确实返回了一些行，这些行的值在 mycolumn 等于'A'。因此，我很惊讶。

Strangely enough, the query above did return some lines for which the value inside mycolumn was equal to 'A'. I was very surprised.

因此，为了了解正在发生的事情，我尝试进行一个非常简单的逻辑测试：

Therefore, to understand what was happening I tried to carry out a very simple logical test:

SELECT ('A' ILIKE ANY(ARRAY['A','S'])) as logical_test ;

上面的语句返回TRUE，这是预期的。

The statement above returns TRUE, which was expected.

但是以下语句也返回TRUE，这就是我迷路的地方：

But the following statement also returns TRUE and this is where I'm lost:

SELECT ('A' NOT ILIKE ANY(ARRAY['A','S'])) as logical_test ;

有人可以解释为什么'A'不像任何人（ARRAY ['A '，'S']）被PostgreSQL认为是TRUE吗？

Could someone explain why 'A' NOT ILIKE ANY(ARRAY['A','S']) is considered TRUE by PostgreSQL?

推荐答案

ANY的结果如果数组中的至少一个元素符合条件，则compare为true。

The result of the ANY comparison is true if at least one element from the array qualifies for the condition.

由于'S'与'A' ANY 的结果为真（因为至少一个元素不同）。

As 'S' is different from 'A' the result of ANY is true (because at least one element was different).

您正在寻找 ALL 运算符：

SELECT * 
FROM mytable 
WHERE mycolumn NOT ILIKE ALL(ARRAY['A','S'])

这篇关于了解数组的ILIKE ANY元素-Postgresql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！