PostgreSQL对数组不区分大小写的SELECT [英] PostgreSQL case insensitive SELECT on array

问题描述

我在Google或文档中无法找到答案...

我需要对数组类型进行不区分大小写的选择。

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.

因此，如果：

value = {"Foo","bar","bAz"}

我需要

SELECT value FROM table WHERE 'foo' = ANY(value)

中选择值。

我尝试了很多Lower（）组合，但均未成功。

I've tried lots of combinations of lower() with no success.

用ILIKE 代替 = 似乎有效，但我一直对 Like 感到紧张-是最好的方法吗？

ILIKE instead of = seems to work but I've always been nervous about LIKE - is that the best way?

推荐答案

一个未提及的替代方法是安装 PostgreSQL 8.4+随附的 citext 扩展名，并使用数组 citext ：

One alternative not mentioned is to install the citext extension that comes with PostgreSQL 8.4+ and use an array of citext:

regress=# CREATE EXTENSION citext;
regress=# SELECT 'foo' = ANY( '{"Foo","bar","bAz"}'::citext[] );
 ?column? 
----------
 t
(1 row)

如果您想严格正确地避免扩展，则必须做一些非常难看的子查询，因为Pg没有很多丰富的数组操作，特别是没有功能映射操作。像这样的东西：

If you want to be strictly correct about this and avoid extensions you have to do some pretty ugly subqueries because Pg doesn't have many rich array operations, in particular no functional mapping operations. Something like:

SELECT array_agg(lower(($1)[n])) FROM generate_subscripts($1,1) n;

...其中$ 1是数组参数。在您的情况下，我认为您可以作弊，因为您不关心保留数组的顺序，因此您可以执行以下操作：

... 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:

SELECT 'foo' IN (SELECT lower(x) FROM unnest('{"Foo","bar","bAz"}'::text[]) x);

这篇关于PostgreSQL对数组不区分大小写的SELECT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！