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
Thanks, Karthika
Use the ANY operator:
where 1 = ANY (array_column)
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);
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
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.
data to sample_data in the from clauseCheck the contains operator @>
select array[1,2] @> array[1];
?column?
----------
t
http://www.postgresql.org/docs/current/static/functions-array.html
