对Postgres字符串数组不敏感的索引
我在Postgres 9.2中使用varchar[]列（varchar数组）来存储一些标签。 在通过标签检索行时，我希望查询不区分大小写。 但是，我想保留在UI中显示的案例（因此我不能将所有内容都存储为小写）。
所以，我的问题是如何在PostgreSQL中通过varchar数组创建不区分大小写的索引？ 一种可能的方法是在列上创建一个函数GIN索引。 如何做到这一点？ 任何其他方法？
@Saurabh Nanda：与您发布的内容类似，您还可以创建一个简单函数将varchar数组转换为小写，如下所示：
CREATE OR REPLACE FUNCTION array_lowercase(varchar[]) RETURNS varchar[] AS
$BODY$
SELECT array_agg(q.tag) FROM (
SELECT btrim(lower(unnest($1)))::varchar AS tag
) AS q;
$BODY$
language sql IMMUTABLE;
请注意，我也在修剪空格标记。 这对你来说可能不是必要的，但我通常会为了一致性而做。
测试：
SELECT array_lowercase(array['Hello','WOrLD']);
array_lowercase
-----------------
{hello,world}
(1 row)
正如Saurabh指出的那样，您可以创建一个GIN索引：
CREATE INDEX ix_tags ON tagtable USING GIN(array_lowercase(tags));
并查询：
SELECT * FROM tagtable WHERE ARRAY['mytag'::varchar] && array_lowercase(tags);
更新： WHILE vs array_agg / unnest的性能
我创建了100K 10个元素的text[]数组（12个字符的随机混合大小写字符串）并测试了每个函数。
array_agg / unnest函数返回：
EXPLAIN ANALYZE VERBOSE SELECT array_lowercase(data) FROM test;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on public.test (cost=0.00..28703.00 rows=100000 width=184) (actual time=0.320..3041.292 rows=100000 loops=1)
Output: array_lowercase((data)::character varying[])
Total runtime: 3174.690 ms
(3 rows)
WHILE函数返回：
EXPLAIN ANALYZE VERBOSE SELECT array_lowercase_while(data) FROM test;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on public.test (cost=0.00..28703.00 rows=100000 width=184) (actual time=5.128..4356.647 rows=100000 loops=1)
Output: array_lowercase_while((data)::character varying[])
Total runtime: 4485.226 ms
(3 rows)
更新2： FOREACH与WHILE作为最终的实验，我将WHILE函数改为使用FOREACH：
CREATE OR REPLACE FUNCTION array_lowercase_foreach(p_input varchar[]) RETURNS varchar[] AS $BODY$
DECLARE
el text;
r varchar[];
BEGIN
FOREACH el IN ARRAY p_input LOOP
r := r || btrim(lower(el))::varchar;
END LOOP;
RETURN r;
END;
$BODY$
language 'plpgsql'
结果似乎与WHILE相似：
EXPLAIN ANALYZE VERBOSE SELECT array_lowercase_foreach(data) FROM test;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on public.test (cost=0.00..28703.00 rows=100000 width=184) (actual time=0.707..4106.867 rows=100000 loops=1)
Output: array_lowercase_foreach((data)::character varying[])
Total runtime: 4239.958 ms
(3 rows)
尽管我的测试没有任何严格的要求，但我确实运行过多个版本，并发现这些数字具有代表性，表明SQL方法（array_agg / unnest）是最快的。
找到一种使用自定义pgplsql函数的可能方法：
首先声明一个自定义函数，该函数将一个varchar []数组作为输入，并返回一个新数组，并将所有元素转换为小写。 （这是我第一次编写PL / SQL，所以这可能是非常低效的代码）。
CREATE OR REPLACE FUNCTION array_lowercase(varchar[]) RETURNS varchar[] AS $$
DECLARE
i INTEGER;
l INTEGER;
r VARCHAR[];
inp ALIAS FOR $1;
BEGIN
i := 1;
l := array_length($1, 1);
WHILE i <= l LOOP
r[i] = lower(inp[i]);
i := i + 1;
END LOOP;
RETURN r;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
接下来，使用新定义的array_lowercase函数在表达式上创建一个GIN索引：
create index hotel_bookings_tags on hotel_bookings using gin(array_lowercase(tags));
现在在查询中使用它（验证它是使用EXPLAIN使用索引）：
select * from posts where array[(varchar 'some_tag')] && array_lowercase(tags);
不知道它有帮助，但我正在寻找类似于text[]东西，并使用了类型转换：
select id from product where lower(tags::text)::text[] && array['tat'];