postgresql Postgres：检查数组字段是否包含值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39643454/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres: check if array field contains value?
提问by Richard
I'm sure this is a duplicate question in the sense that the answer is out there somewhere, but I haven't been able to find the answer after Googling for 10 minutes, so I'd appeal to the editors not to close it on the basis that it might well be useful for other people.
我确定这是一个重复的问题，因为答案就在某个地方，但在谷歌搜索 10 分钟后我一直无法找到答案，所以我呼吁编辑不要关闭它它可能对其他人有用的基础。
I'm using Postgres 9.5. This is my table:
我正在使用 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
I want to find all the rows with "Journal" in pub_types.
我想在pub_types.
I've found the docs and googled and this is what I've tried:
我找到了文档并用谷歌搜索，这就是我尝试过的：
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";
I've scanned the postgres array docsbut can't see a simple example of how to run a query, and StackOverflow questions all seem to be based around more complicated examples.
我已经扫描了 postgres 数组文档，但看不到如何运行查询的简单示例，而且 StackOverflow 问题似乎都基于更复杂的示例。
回答by redneb
This should work:
这应该有效：
select * from mytable where 'Journal'=ANY(pub_types);
i.e. the syntax is <value> = ANY ( <array> ). Also notice that string literals in postresql are written with single quotes.
即语法是<value> = ANY ( <array> ). 还要注意 postresql 中的字符串文字是用单引号写的。
回答by Sudharsan Thumatti
With ANYoperator you can search for only one value.
使用ANY运算符，您只能搜索一个值。
For example,
例如，
select * from mytable where 'Book' = ANY(pub_types);
If you want to search multiple values, you can use @>operator.
如果要搜索多个值，可以使用@>运算符。
For example,
例如，
select * from mytable where pub_types @> '{"Journal", "Book"}';
You can specify in which ever order you like.
您可以指定您喜欢的任何顺序。
回答by Shane
This worked for me:
这对我有用：
select * from mytable
where array_to_string(pub_types, ',') like '%Journal%'
Depending on your normalization needs, it might be better to implement a separate table with a FK reference as you may get better performance and manageability.
根据您的规范化需求，最好使用 FK 引用实现单独的表，因为您可以获得更好的性能和可管理性。
回答by Cepr0
Instead of INwe can use ANYwith arrays casted to enum array, for example:
相反的IN，我们可以使用ANY同一个转换到枚举阵列阵列，例如：
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[]);
Or we can still use 'IN' clause, but first, we should 'unnest' it:
或者我们仍然可以使用 'IN' 子句，但首先，我们应该'unnest'它：
select
*
from
example_table t
where
t.enum_field in (select unnest(array['ENUM1', 'ENUM2']::example_enum[]));
Example: https://www.db-fiddle.com/f/LaUNi42HVuL2WufxQyEiC/0
回答by Srikanth
This worked for me
这对我有用
let exampleArray = [1, 2, 3, 4, 5];
let exampleToString = exampleArray.toString(); //convert to toString
let query = `Select * from table_name where column_name in (${exampleToString})`; //Execute the query to get response
I have got the same problem, then after an hour of effort I got to know that the array should not be directly accessed in the query. So I then found that the data should be sent in the paranthesis it self, then again I have converted that array to string using toString method in js. So I have worked by executing the above query and got my expected result
我遇到了同样的问题，经过一个小时的努力，我知道不应在查询中直接访问数组。所以我发现数据应该在它自己的括号中发送，然后我再次使用 js 中的 toString 方法将该数组转换为字符串。所以我通过执行上述查询并得到了我的预期结果
