在Postgres数组中检查值是否存在

Question

我正在使用Postgres 9.0，需要一种方法来测试给定数组中是否存在某个值。到目前为止，我想到了以下这个：

select '{1,2,3}'::int[] @> (ARRAY[]::int[] || value_variable::int)

但我一直在想，应该有一种更简单的方法，只是我无法看到它。这个似乎更好：

select '{1,2,3}'::int[] @> ARRAY[value_variable::int]

我相信这已经足够了。但如果您有其他方法，请分享！

- Mike Starov

Answer 1

更简单的方法是使用ANY结构：

SELECT value_variable = ANY ('{1,2,3}'::int[])

任何（括号中的右操作数）可以是集合（例如子查询的结果）或数组。有几种使用方法：

• SQLAlchemy：如何在PgArray列类型上进行过滤？
• PostgreSQL中的IN vs ANY运算符

重要的区别：数组运算符（<@，@>，&&等）期望操作数为数组类型，并且在PostgreSQL的标准发行版中支持GIN或GiST索引，而ANY结构则期望左操作数为元素类型，可以使用普通的B-tree索引进行支持（索引表达式位于运算符的左侧，而不是像您的示例中看起来的那样在另一侧）。 例如：

• 查找JSON数组中元素的索引

所有这些都无法处理NULL元素。 要测试NULL：

• 检查 Postgres 数组中是否存在 NULL 值

- Erwin Brandstetter

---

@Ramprasad：List 意味着列表。在示例中，它被转换为数组。我没有表述清楚的是：对于<@运算符，两侧都必须是一个数组：ARRAY[s.employee_id] <@ '+list_of_employeeIDs+'::int[] 数组语法示例。 - Erwin Brandstetter

@Ramprasad：那就把它变成一个数组。如果仍然不清楚，请提一个新问题（而不是评论）。 - Erwin Brandstetter

尽管在互联网年代这已经是一个老掉牙的问题了，像我这样思维缓慢的人也应该知道，在WHERE子句中可以使用'something' = ANY(some_array)。出于某些原因，只有Crom知道，我过去四年一直以为不能在WHERE子句中使用数组比较器。但现在那些日子已经过去了。（或许只是我小时候被摔了一下头，所以才会这么想）。 - GT.

要点是：在“WHERE”子句中，任何boolean表达式都可以使用——取决于Crom是否愿意。 - Erwin Brandstetter

如果你对将其解密为普通的SQL查询有困难，这里有一个例子：SELECT id FROM conversation WHERE 45043182 = ANY(participant_user_ids) ; - oligofren

Answer 2

当你想要检查一个数组中是否没有某个特定的值时，请注意陷阱：不要这样做：

SELECT value_variable != ANY('{1,2,3}'::int[])

但使用

SELECT value_variable != ALL('{1,2,3}'::int[])

相反。

- murison

---

双重否定的用法；注意他使用了“ALL”和“ANY”的不同。 - vol7ron

SELECT NOT value_variable = ANY('{1,2,3}'::int[]) 可能更易读。 - Ondřej Bouda

Answer 3

但是如果您有其他方法，请分享。

您可以将两个数组进行比较。如果左侧数组中的任何值与右侧数组中的值重叠，则返回 true。这种方法有点取巧，但有效。

SELECT '{1}'   && '{1,2,3}'::int[];  -- true
SELECT '{1,4}' && '{1,2,3}'::int[];  -- true
SELECT '{4}'   && '{1,2,3}'::int[];  -- false

• 在第一和第二个查询中，值1位于右侧数组中
• 请注意，第二个查询是true，即使值4不包含在右侧数组中
• 对于第三个查询，左侧数组中没有任何值（即4）包含在右侧数组中，因此返回false

- vol7ron

---

我该如何从另一个表中搜索一列以在数组中具有值？例如：select * from beers where style_id in (select preferences from users where id=1) limit 1;style_id是整数数据类型；preferences是integer[]我收到了这个错误ERROR: operator does not exist: integer = integer[] LINE 1: select * from beers where style_id in (select preferences f... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. - H P

@HP 有不同的方法来解决这个问题，你应该提出一个新问题。 - vol7ron

你确定没有现有的问题吗？@vol7ron - H P

@HP 完全不是，但评论是用于针对问题或答案的评论；通常是为了添加更多信息或征求未被解决的更多信息。您正在提出与此答案无关的问题。我认为您通过发布新帖子而不是在评论中提问会更有好运气;) - vol7ron

@HP 如果您还没有发布您的问题，可以在此处查看：http://sqlfiddle.com/#!15/144cd/3，以了解您需要做什么--您的问题不同，因为您需要展开您的数组。 - vol7ron

Answer 4

unnest 可以被使用来展开数组并转换为一组行，然后检查一个值是否存在就像使用 IN 或 NOT IN 一样简单。

例如：

1. id => uuid

2. exception_list_ids => uuid[]

select * from table where id NOT IN (select unnest(exception_list_ids) from table2)

- pg2286

---

是的。请注意，在我的查询计划中，SELECT UNNEST 不如 = ANY 好。我建议检查查询计划以查看是否得到所需的结果。 - Rob Bygrave

Answer 5

嗨，那个对我来说很好用，也许对其他人有用。

select * from your_table where array_column ::text ilike ANY (ARRAY['%text_to_search%'::text]);

- Dave Kraczo

Answer 6

"任何" 都可以使用。只需确保 "any" 关键字在等于号的右侧，即在等于号之后出现。

下面的语句会抛出错误: ERROR: syntax error at or near "any"。

select 1 where any('{hello}'::text[]) = 'hello';

以下示例可以正常工作：

select 1 where 'hello' = any('{hello}'::text[]);

- Debasish Mitra

---

这应该是一个更高投票的答案。它还澄清了类型是text[]而不是string[]，许多人如果他们有其他编程语言的经验可能会假设错误。 - Sidharth Ghoshal

Answer 7

在查找数组中的元素时，需要进行适当的类型转换以通过Postgres的SQL解析器。以下是使用包含数组操作符的连接子句中的一个示例查询：

为了简单起见，我仅列出相关部分：

table1 other_name text[]; -- is an array of text

SQL中的连接部分如下所示。

from table1 t1 join table2 t2 on t1.other_name::text[] @> ARRAY[t2.panel::text]

下面的方法也可以生效。

on t2.panel = ANY(t1.other_name)

我猜测额外的转换是必要的，因为解析过程不需要获取表定义来确定列的确切类型。其他人请就此发表评论。

- Kemin Zhou

Answer 8

对于@ErwinBrandstetter的已接受答案，还有一个额外的注意事项：如果您想在与单个值进行比较时利用数组列上的索引，可以像这样将值包装在数组中：

select '{1,2,3}'::int[] @> ARRAY[value_variable]

但是，在使用参数时可能需要转换数组类型：

select '{1,2,3}'::int[] @> ARRAY[@value_variable]::int[]

- Morten Christiansen