Postgres不区分大小写的查询，带有包含数组的操作

Question

我有一些记录，其中包含如下标签数组:

   id   |                     title                     |       tags
--------+-----------------------------------------------+----------------------
 124009 | bridge photo                                  | {bridge,photo,Colors}
 124018 | Zoom 5                                        | {Recorder,zoom}
 123570 | Sint et                                       | {Reiciendis,praesentium}
 119479 | Architecto consectetur                        | {quia}

我正在使用以下的SQL查询语句，通过标签（'bridge'，'photo'，'Colors'）来获取特定的记录：

SELECT  "listings".* FROM "listings" WHERE (tags @> ARRAY['bridge', 'photo', 'Colors']::varchar[]) ORDER BY "listings"."id" ASC LIMIT $1  [["LIMIT", 1]]

这将返回此表中的第一条记录。

问题在于我有混合类型情况，并且如果我搜索 bridge, photo, colors，希望返回同样的结果。实质上，我需要使此搜索不区分大小写，但无法找到在Postgres中执行此操作的方法。

这是我尝试的 SQL 查询，但出现错误：

SELECT  "listings".* FROM "listings" WHERE (LOWER(tags) @> ARRAY['bridge', 'photo', 'colors']::varchar[]) ORDER BY "listings"."id" ASC LIMIT $1

以下是错误信息：

PG::UndefinedFunction: 错误：函数 lower(character varying[]) 不存在
提示：没有匹配给定名称和参数类型的函数。您可能需要添加显式类型转换。

注：该错误与PG数据库中的函数匹配有关，需要添加显式类型转换以解决问题。

- shime

Answer 1

您可以按照以下方式将文本数组元素转换为小写：

select lower(tags::text)::text[]
from listings;

          lower           
--------------------------
 {bridge,photo,colors}
 {recorder,zoom}
 {reiciendis,praesentium}
 {quia}
(4 rows)

在查询中使用以下内容：

SELECT * 
FROM listings
WHERE lower(tags::text)::text[] @> ARRAY['bridge', 'photo', 'colors'] 
ORDER BY id ASC;

   id   |    title     |         tags          
--------+--------------+-----------------------
 124009 | bridge photo | {bridge,photo,Colors}
(1 row) 

- klin

---

lower(tags::text)::text[] 在所有情况下都能正常工作，但仍然感觉很不优雅。 - pozs

脏？这只是个人口味问题。脏但快！;) - klin

Answer 2

您不能直接对数组应用 LOWER() 函数，但是您可以对数组进行解包，对每个元素应用函数，然后在完成后重新组装数组：

... WHERE ARRAY(SELECT LOWER(UNNEST(tags))) @> ARRAY['bridge', 'photo', 'colors']

您还可以安装citext（不区分大小写的文本）模块；如果将listings.tags声明为类型citext[]，则查询应该可以正常工作。

- Nick Barnes

---

谢谢Nick，接受了另一个答案，因为它不需要安装额外的扩展。 - shime