在 Postgres 中搜索整数数组

发布于 2024-12-17 15:36:55 字数 356 浏览 1 评论 0原文

有没有其他方法可以在 Postgres 的 integer[] 列中搜索特定值？

我当前安装的 Postgres 版本不允许允许以下语句：

SELECT * FROM table WHERE values *= 10;

数组示例：

'{11043,10859,10860,10710,10860,10877,10895,11251}'
'{11311,10698,10697,10710,10712,10711,10708}'

该语句应返回数组包含 '10710' 的每一行。

原文

Is there any other way to search for a certain value in an integer[] column in Postgres?

My currently installed Postgres version does not allow the following statement:

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'.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

冰葑 2024-12-24 15:36:55

对于相等性检查，您可以简单地：

SELECT * FROM tbl WHERE 10 = ANY (values);

阅读有关 ANY/手册中的一些内容。
对于大表/大数组，请考虑数组运算符具有索引支持，例如：

SELECT * FROM tbl WHERE values @> '{10}'::int[];

参见：

检查Postgres数组中是否存在值
<一个href="https://stackoverflow.com/questions/34627026/in-vs-any-operator-in-postgresql/34627688#34627688">IN 与 PostgreSQL 中的 ANY 运算符

对于整数数组，请考虑附加模块 < a href="https://www.postgresql.org/docs/current/intarray.html" rel="noreferrer">intarray：

比较数组是否相等，忽略元素顺序

相关：

For equality checks you can simply:

SELECT * FROM tbl WHERE 10 = ANY (values);

Read about ANY/SOME in the manual.
For big tables / big arrays consider array operators with index support, like:

SELECT * FROM tbl WHERE values @> '{10}'::int[];

See:

For integer arrays consider the additional module intarray:

Compare arrays for equality, ignoring order of elements

回复收藏 0 原文

高冷爸爸 2024-12-24 15:36:55

快速搜索也会如此，但您应该使用索引要点或 gin 作为 intarray 类型 Postgres intarray

 SELECT * FROM table WHERE values @> ARRAY[10];

quickly search will be so, but you should use index gist or gin for intarray type Postgres intarray

 SELECT * FROM table WHERE values @> ARRAY[10];

回复收藏 0 原文

著墨染雨君画夕 2024-12-24 15:36:55

**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

**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

回复收藏 0 原文

~没有更多了~