PostgreSQL 9.6中二维数组匹配一维数组列查询报错求解

你遇到的ERROR: could not find array type for data type text[]错误，核心原因是类型不匹配：你的column_name是varchar(255)[]（一维变长字符数组），而你传给ANY()的是未指定类型的二维text数组，PostgreSQL无法自动完成类型转换，也无法直接用二维数组和一维数组做相等匹配。

正确的查询方法 #

你需要先把二维数组转换成和column_name同基类型的二维数组，再通过unnest()将其拆分为一维数组的集合，让ANY()可以逐个比较匹配。

方法1：使用ANY + unnest

SELECT * 
FROM new_table s 
WHERE s.column_name = ANY(unnest('{{"one", "two"}, {"one", "more"}, {"two", "five"}}'::varchar(255)[][]));

方法2：使用IN + unnest（效果等价）

SELECT * 
FROM new_table s 
WHERE s.column_name IN (SELECT unnest('{{"one", "two"}, {"one", "more"}, {"two", "five"}}'::varchar(255)[][]));

结果验证 #

执行上述任意语句后，都会得到你期望的结果：

column_name |    name
-------------+--------------
 {one,two}   | first_user
 {one,more}  | third_user

错误原因再解释 #

1. 你原查询中的'{{"one", "two"}, ...}'默认被解析为text[][]类型，和column_name的varchar(255)[]类型不兼容，所以需要用::varchar(255)[][]显式转换类型。
2. ANY()操作符用于一维数组时，是逐个比较元素；但面对二维数组时，它无法直接识别你要匹配的是数组中的一维子数组，必须用unnest()把二维数组“展开”成多个独立的一维数组，才能和column_name做相等匹配。

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