搜索数组中的元素而在PostgreSQL中没有循环 [英] searching an element in an array without a loop in postgresql

问题描述

我正在编写查询以搜索数组中的元素。使用 for循环的搜索效率不高，因为我的数组包含很多元素。因此，查询要花费大量时间来执行。因此，任何人都可以说如何在没有 for循环的情况下搜索数组中的元素，而循环应该更快。我必须在搜索中获取索引

Im writing a query for searching an element in an array. Search using "for" loop is not efficient because my array is having a lot of elements. Because of this the query is taking lot of time to execute. So can any one say how to search an element in an array without "for" loop which should be faster. I have to get the index on searching

谢谢，
Karthika

Thanks, Karthika

推荐答案

使用ANY运算符：

where 1 = ANY (array_column) 

这将返回其中 array_column 包含值 1的所有行至少一次。如果要检查多个值，请参见Clodoaldo的答案。

That will return all rows where array_column contains the value 1 at least once. If you want to check for multiple values, see Clodoaldo's answer.

如果在该列上创建索引，则应该很快。像这样：

If you create an index on that column, this should be very fast. Something like this:

create index on the_table using gin (the_array_column);

以下内容受此处所示解决方案的启发：使用sample_data（pk_column，array_data）在PostgreSQL数组中查找值的位置

The following is inspired by the solution shown here: Finding the position of a value in PostgreSQL arrays

with sample_data (pk_column, array_data) as (
   values 
     (1, array[1,2,3,4,5]),
     (2, array[7,8,9,11]),
     (3, array[5,4,3,2,1]),
     (4, array[10,9,8,1,4,6]),
     (5, array[7,8,9])
)
select *
from (
  select pk_column, 
         unnest(array_data) as value, 
         generate_subscripts(array_data, 1) as array_index
  from sample_data
  where 1 = any(array_data)
) t
where value = 1

内部where会将需要完成的总工作减少到仅实际包含该值的那些行。然后，外部查询将分解数组以获取值的索引。但是，使用链接的问题中显示的功能实际上可能就是您所追求的。

The inner where will reduce the total work that needs to be done to only those rows that actually contain the value. The outer query will then "explode" the array to get the value's index. But using the function shown in the linked question might actually be what you are after.

这篇关于搜索数组中的元素而在PostgreSQL中没有循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！