如何在PostgreSQL数组中执行'%stars%'模糊查询？

这个问题我碰到过好几次，PostgreSQL里处理数组列的模糊匹配确实得用点小技巧，下面给你几种靠谱的解决方法：

方法1：展开数组后匹配（最直观） #

把数组拆分成单个元素的行，匹配符合条件的元素后再关联回原表，用DISTINCT避免重复返回同一记录：

SELECT DISTINCT t.*
FROM your_table t
JOIN unnest(t.alternate_names) AS arr_val
ON arr_val ILIKE '%stars%';

解释：unnest()函数会把数组的每个元素单独拆成一行，然后我们检查每个元素是否模糊匹配%stars%，最后用DISTINCT确保原表的每条记录只返回一次，哪怕数组里有多个匹配的元素。

方法2：转成字符串后匹配（最简单） #

用array_to_string()把数组拼接成一个字符串，直接对字符串做模糊匹配：

SELECT *
FROM your_table
WHERE array_to_string(alternate_names, ', ') ILIKE '%stars%';

解释：这里用逗号加空格作为分隔符（你可以换成任意不会出现在数组元素里的字符），把整个数组转成类似"All Stars, Foo Bar"的字符串，然后直接用ILIKE匹配。如果你的数组元素里不会出现分隔符，这种方法最简洁。

方法3：用EXISTS子查询（性能友好） #

通过子查询检查数组中是否存在符合条件的元素，逻辑更清晰，也避免了重复记录的问题：

SELECT *
FROM your_table
WHERE EXISTS (
    SELECT 1
    FROM unnest(alternate_names) arr_val
    WHERE arr_val ILIKE '%stars%'
);

解释：EXISTS子查询会在找到第一个匹配的元素后就停止检索，对比方法1的JOIN+DISTINCT，在数组元素较多时性能可能更好。

进阶：优化模糊匹配性能 #

如果你的表数据量很大，想让模糊匹配更快，可以借助pg_trgm扩展创建GIN索引：

1. 先启用扩展：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

2. 创建针对数组列的trgm索引：

CREATE INDEX idx_alternate_names_trgm ON your_table USING GIN (alternate_names gin_trgm_ops);

创建索引后，上面的查询语句会自动利用索引加速，大幅提升模糊匹配的效率。

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