SQL 在 Postgres 中搜索整数数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8242643/
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
Search in integer array in Postgres
提问by jussi
Is there any other way to search for a certain value in an integer[]column in Postgres?
有没有其他方法可以integer[]在 Postgres的列中搜索某个值？
My currently installed Postgres version does notallow the following statement:
我目前安装的Postgres版本并没有让下面的语句：
SELECT * FROM table WHERE values *= 10;
Array examples:
数组示例：
'{11043,10859,10860,10710,10860,10877,10895,11251}'
'{11311,10698,10697,10710,10712,10711,10708}'
The statement should return every row where the array contains '10710'.
该语句应返回数组包含的每一行'10710'。
回答by Erwin Brandstetter
For equality checks you can simply:
对于平等检查，您可以简单地：
SELECT * FROM table WHERE 10 = ANY (values);
Read about ANY/SOME in the manual.
回答by anydasa
quickly search will be so, but you should use index gist or gin for intarray type Postgres intarray
快速搜索将是这样，但你应该使用索引要点或 gin 为 intarray 类型Postgres intarray
SELECT * FROM table WHERE values @> ARRAY[10];
回答by SateeshKasaboina
**Store Integer Array as Strings in Postgresql and Query the Array**
Finally I could save the integer as string array in one column able to successfully convert into array and query the array using below example.
CREATE TABLE test
(
year character varying,
id serial NOT NULL,
category_id character varying,
CONSTRAINT test_pkey PRIMARY KEY (id)
)
Data
"2005";1;"1,2,3,4"
"2006";2;"2,3,5,6"
"2006";3;"4,3,5,6"
"2007";7;"1,2"
select distinct(id) from test, (select id as cid, unnest(string_to_array(category_id , ',')::integer[]) as cat from test) c where c.cid=test.id and cat in (1,2,3);
Result:
2
1
3
7
