如何在PostgreSQL中为text[]类型字段创建不区分大小写的GIN索引？

问题原因 #

你创建生成列时报错，是因为PostgreSQL的生成列表达式不允许包含子查询，所以(select array_agg(upper(n)) from unnest(col) as n)这种写法不被支持。

可行方案 #

方案一：自定义函数+生成列+GIN索引

1. 先创建一个确定性函数，用来将text[]数组的每个元素转成大写：

CREATE OR REPLACE FUNCTION array_upper(text[]) RETURNS text[]
LANGUAGE sql IMMUTABLE
AS $$
SELECT array_agg(upper(unnest)) FROM unnest($1)
$$;

2. 基于这个函数创建生成列：

ALTER TABLE tab ADD COLUMN norm text[] GENERATED ALWAYS AS (array_upper(col)) STORED;

3. 给生成列创建GIN索引：

CREATE INDEX idx_tab_norm_gin ON tab USING GIN(norm);

4. 查询时直接使用生成列做交集判断（可自动处理参数大小写）：

SELECT * FROM tab WHERE norm && array_upper(ARRAY['ay', 'Bee']::text[]);

方案二：直接创建函数型GIN索引（无需生成列）

如果不想新增列，可以直接基于自定义函数创建GIN索引：

1. 同样先创建上面的array_upper函数
2. 创建函数索引：

CREATE INDEX idx_tab_col_upper_gin ON tab USING GIN(array_upper(col));

3. 查询时调用函数进行判断：

SELECT * FROM tab WHERE array_upper(col) && array_upper(ARRAY['ay', 'Bee']::text[]);

方案三：使用citext数组（最简洁）

PostgreSQL的citext扩展支持不区分大小写的字符串比较，我们可以把字段转成citext[]类型：

1. 先启用citext扩展：

CREATE EXTENSION IF NOT EXISTS citext;

2. 修改字段类型：

ALTER TABLE tab ALTER COLUMN col TYPE citext[];

3. 创建GIN索引：

CREATE INDEX idx_tab_col_citext_gin ON tab USING GIN(col);

4. 查询时直接使用&&操作符，自动忽略大小写：

SELECT * FROM tab WHERE col && ARRAY['ay', 'Bee']::citext[];

内容的提问来源于stack exchange，提问作者Gunther Schadow