Postgres：检查数组字段是否包含值？

Question

我相信这是一个重复的问题，因为答案在某个地方已经存在，但是在谷歌搜索了10分钟后，我还是没有找到答案，所以我要求编辑们不要基于这个问题可能对其他人有用而关闭它。

我正在使用Postgres 9.5。这是我的表格：

        Column          │           Type            │                                Modifiers
────────────────────────┼───────────────────────────┼─────────────────────────────────────────────────────────────────────────
 id                     │ integer                   │ not null default nextval('mytable_id_seq'::regclass)
 pmid                   │ character varying(200)    │
 pub_types              │ character varying(2000)[] │ not null

我想找到所有在pub_types中含有"Journal"的行。

我已经找到了文档并进行了谷歌搜索，这是我尝试过的方法：

select * from mytable where ("Journal") IN pub_types;
select * from mytable where "Journal" IN pub_types;
select * from mytable where pub_types=ANY("Journal");
select * from mytable where pub_types IN ("Journal");
select * from mytable where where pub_types contains "Journal";

我已经浏览了the postgres array docs，但没有看到一个简单的运行查询的例子，而且StackOverflow上的问题似乎都是基于更复杂的例子。

- Richard

---

注意：在PostgreSQL中，对于字符串请使用单引号。双引号用于分隔符名称（例如列、索引等）。 - François Leblanc

这个回答解决了你的问题吗？在Postgres数组中检查值是否存在 - Johan Maes

确实存在重复。这是被接受的答案：https://dev59.com/0Ggu5IYBdhLWcg3wkH2P#11231965。请务必首先检查该答案，因为它包含有关性能的重要信息。 - Johan Maes

Answer 1

这应该可以运行：

select * from mytable where 'Journal'=ANY(pub_types);

即语法为<value> = ANY ( <array> )。同时请注意，在PostgreSQL中，字符串字面量需用单引号表示。

- redneb

---

如果我想检查一个数组字段是否包含另一个数组中的元素，该怎么办？ - Aaron

我收到了“ERROR: input of anonymous composite types is not implemented”的错误。 - Shanerk

IN 需要显式指定数值列表（或子查询），而 ANY 则可以使用数组。如果您已经将数值列表存储在数组中，例如当数组存储在数据库的某个列中时，则这很有用，正如 OP 的情况。 - redneb

关于性能方面，这个语法怎么样？ select * from mytable where pub_types @> array['Journal'::text]; - Nina

什么是索引？gin - morfair

Answer 2

使用任何运算符，您可以仅搜索一个值。

例如，

SELECT * FROM mytable WHERE 'Book' = ANY(pub_types);

如果你想搜索一个包含多个值的数组，你可以使用@> 运算符。

例如，

SELECT * FROM mytable WHERE pub_types @> '{"Journal", "Book"}';

如果您想在包含多个值的数组中搜索其中一个值，可以使用 && 运算符。

例如，

SELECT * FROM mytable WHERE pub_types && '{"Journal", "Book"}';

您可以按任何顺序指定。

- Sudharsan Thumatti

---

@>表示包含数组中的所有值。如果您想搜索当前数组是否包含另一个数组中的任何值，可以使用&&。select * from mytable where pub_types && '{"Journal", "Book"}'; - jallen0927

我不知道这是否与版本有关，但在Postgres 9.6中，@>和&&两者对我来说完全相同。它们都匹配列表中的任何项，除了@>还匹配空列表'{}'。 - Marcelus Trojahn

请注意，在预处理语句中，@> '{"Journal", "Book"}' 语法似乎无法正常工作（当提供 @> '{$1, $2}' 时，postgres 无法将 $1 视为值占位符）。因此，我使用了这种替代语法（在我看来更加简洁）：WHERE pub_types @> array['Journal', 'Book']; - Venryx

如果 pub_type 是空值，它还能正常工作吗？ - Ashish

Answer 3

使用Postgres的array_to_string()方法使我能够匹配数组中包含“Journal”的字符串部分：

select * from mytable
where array_to_string(pub_types, ',') like '%Journal%'

如果您只想匹配精确单词“Journal”，请删除通配符：

select * from mytable
where array_to_string(pub_types, ',') = 'Journal'

参考资料：https://www.postgresql.org/docs/9.1/functions-array.html

- Shane Kenyon

---

如果您有多个具有相同前缀的值，即“Journal Entries”，则这将产生误报。 - halfdan

从OP提出的问题来看，他似乎想在字符串中找到任何出现的“Journal”。如果你只想匹配它是单词“Journal”的位置，只需删除前导和尾随通配符字符（即%）。 - Shanerk

很好——让我在数组上执行了一个 ILIKE 查询；谢谢！SELECT * FROM archive WHERE ARRAY_TO_STRING(kw, ',') ILIKE '%pLASt%'; - Victoria Stuart

@PeterGerdes：你没有看错。这个答案中有错误信息。 - Erwin Brandstetter

我猜我误读了文档，所以从答案中删除了那部分。做性能基准测试可能很有趣，但归根结底，这两种方法都可以工作。 - Shanerk

Answer 4

我们可以将数组转换为枚举数组，使用ANY代替IN，例如：

create type example_enum as enum (
  'ENUM1', 'ENUM2'
);

create table example_table (
  id integer,
  enum_field example_enum
);

select 
  * 
from 
  example_table t
where
  t.enum_field = any(array['ENUM1', 'ENUM2']::example_enum[]);

或者我们仍然可以使用“IN”子句，但首先，我们应该将其“展开”：

select 
  * 
from 
  example_table t
where
  t.enum_field in (select unnest(array['ENUM1', 'ENUM2']::example_enum[]));

例子： https：//www.db-fiddle.com/f/LaUNi42HVuL2WufxQyEiC/0

（该链接为it技术相关内容）

- Cepr0