PostgreSQL 不区分大小写的 SELECT 数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16311289/
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 case insensitive SELECT on array
提问by PerryW
I'm having problems finding the answer here, on google or in the docs ...
I need to do a case insensitive select against an array type.
我在这里，在谷歌或在文档中找到答案时遇到问题......
我需要对数组类型进行不区分大小写的选择。
So if:
因此，如果：
value = {"Foo","bar","bAz"}
I need
我需要
SELECT value FROM table WHERE 'foo' = ANY(value)
to match.
匹配。
I've tried lots of combinations of lower() with no success.
我已经尝试了很多 lower() 的组合，但都没有成功。
ILIKEinstead of =seems to work but I've always been nervous about LIKE- is that the best way?
ILIKE而不是=似乎有效，但我一直很紧张LIKE- 这是最好的方法吗？
采纳答案by Craig Ringer
One alternative not mentioned is to install the citextextensionthat comes with PostgreSQL 8.4+ and use an array of citext:
未提及的一种替代方法是安装PostgreSQL 8.4+ 附带的citext扩展并使用以下数组citext：
regress=# CREATE EXTENSION citext;
regress=# SELECT 'foo' = ANY( '{"Foo","bar","bAz"}'::citext[] );
?column?
----------
t
(1 row)
If you want to be strictly correct about this and avoid extensions you have to do some pretty ugly subqueriesbecause Pg doesn't have many rich array operations, in particular no functional mapping operations. Something like:
如果您想对此严格正确并避免扩展，您必须执行一些非常丑陋的子查询，因为 Pg 没有很多丰富的数组操作，特别是没有功能映射操作。就像是：
SELECT array_agg(lower(()[n])) FROM generate_subscripts(,1) n;
... where $1 is the array parameter. In your case I think you can cheat a bit because you don't care about preserving the array's order, so you can do something like:
... 其中 $1 是数组参数。在您的情况下，我认为您可以作弊，因为您不关心保留数组的顺序，因此您可以执行以下操作：
SELECT 'foo' IN (SELECT lower(x) FROM unnest('{"Foo","bar","bAz"}'::text[]) x);
回答by Chris Travers
This seems hackish to me but I think it should work
这对我来说似乎很黑，但我认为它应该有效
SELECT value FROM table WHERE 'foo' = ANY(lower(value::text)::text[])
ilikecould have issues if your arrays can have _or %
ilike如果您的阵列可以有_或%
Note that what you are doing is converting the text array to a single text string, converting it to lower case, and then back to an array. This should be safe. If this is not sufficient you could use various combinations of string_to_array and array_to_string, but I think the standard textual representations should be safer.
请注意，您正在做的是将文本数组转换为单个文本字符串，将其转换为小写，然后再转换回数组。这应该是安全的。如果这还不够，您可以使用 string_to_array 和 array_to_string 的各种组合，但我认为标准文本表示应该更安全。
Updatebuilding on subquery solution below, one option would be a simple function:
在下面的子查询解决方案上更新构建，一个选项是一个简单的函数：
CREATE OR REPLACE FUNCTION lower(text[]) RETURNS text[] LANGUAGE SQL IMMUTABLE AS
$$
SELECT array_agg(lower(value)) FROM unnest() value;
$$;
Then you could do:
那么你可以这样做：
SELECT value FROM table WHERE 'foo' = ANY(lower(value));
This might actually be the best approach. You could also create GIN indexes on the output of the function if you want.
这实际上可能是最好的方法。如果需要，您还可以在函数的输出上创建 GIN 索引。
回答by Erwin Brandstetter
Another alternative would be with unnest()
另一种选择是 unnest()
WITH tbl AS (SELECT 1 AS id, '{"Foo","bar","bAz"}'::text[] AS value)
SELECT value
FROM (SELECT id, value, unnest(value) AS val FROM tbl) x
WHERE lower(val) = 'foo'
GROUP BY id, value;
I added an idcolumn to get exactlyidentical results - i.e. duplicate valueif there are duplicates in the base table. Depending on your circumstances, you can probably omit the idfrom the query to collapse duplicates in the results or if there are no dupes to begin with. Also demonstrating a syntax alternative:
我添加了一id列以获得完全相同的结果 - 即value如果基表中有重复项，则重复。根据您的情况，您可能可以id从查询中省略以折叠结果中的重复项，或者如果开始时没有重复项。还演示了一种语法替代方案：
SELECT value
FROM (SELECT value, lower(unnest(value)) AS val FROM tbl) x
WHERE val = 'foo'
GROUP BY value;If array elements are unique within arrays in lower case, you don't even need the GROUP BY, since every valuecan only match once.
如果数组元素在小写的数组中是唯一的，您甚至不需要GROUP BY，因为每个value只能匹配一次。
SELECT value
FROM (SELECT value, lower(unnest(value)) AS val FROM tbl) x
WHERE val = 'foo';
'foo'must be lower case, obviously.
Should be fast.
'foo'显然必须是小写。
应该很快。
If you want that fastwit a big table, I would create a functional GIN index, though.
不过，如果你想要一个大表那么快，我会创建一个功能性 GIN 索引。
回答by Mike
my solution to exclude values using a sub select...
我使用子选择排除值的解决方案......
and groupname not ilike all (
select unnest(array[exceptionname||'%'])
from public.group_exceptions
where ...
and ...
)
回答by user563093
Regular expression may do the job for most cases
正则表达式可以在大多数情况下完成这项工作
SELECT array_to_string('{"a","b","c"}'::text[],'|') ~* ANY('{"A","B","C"}');
SELECT array_to_string('{"a","b","c"}'::text[],'|') ~* ANY('{"A","B","C"}');
