34119

searching an element in an array without a loop in postgresql

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

Answer1:

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.

Answer2:

Check the contains operator @>

select array[1,2] @> array[1]; ?column? ---------- t

http://www.postgresql.org/docs/current/static/functions-array.html

Recommend

  • How to sum array value php
  • How To Get ALL Minimum Value Within Multidimensional Array?
  • BCP from One Table to Another table in different servers in SQL Server 2008 R2
  • Add date without exceeding a month
  • Recursive method 10x slower than interative [closed]
  • stop image from moving when reaching the border of a container
  • Transition height onload of d3js rect svg
  • format JSON value with a php function and print result inside a div#id
  • How can I fast-forward a branch without checking it out
  • How to properly fetch single cells in pandas: loc[index,column] VS get_value(index,column)
  • Randomly placing a polygon inside of polygon
  • finding greatest prime factor using recursion in c
  • jQuery toggle hide on click elsewhere
  • Is there a package like bigmemory in R that can deal with large list objects?
  • Check all values in string[] for length?
  • RxJava debounce by arbitrary value
  • Is there a way to do normal logging with EureakLog?
  • How to avoid particles glitching together in an elastic particle collision simulator?
  • Asynchronous UI Testing in Xcode With Swift
  • Recording logins for password protected directories
  • Repeat a vertical line on every page in Report Builder / SSRS
  • Android screen density dpi vs ppi
  • Splitting given String into two variables - php
  • Bug in WPF DataGrid
  • Check if a string to interpolate provides expected placeholders
  • vba code to select only visible cells in specific column except heading
  • How to model a transition system with SPIN
  • Convert array of 8 bytes to signed long in C++
  • Large data - storage and query
  • Do I've to free mysql result after storing it?
  • Transpose CSV data with awk (pivot transformation)
  • Python: how to group similar lists together in a list of lists?
  • Understanding cpu registers
  • Django query for large number of relationships
  • Recursive/Hierarchical Query Using Postgres
  • Running Map reduces the dimensions of the matrices
  • Sorting a 2D array using the second column C++
  • How can I use `wmic` in a Windows PE script?
  • java string with new operator and a literal
  • How to push additional view controllers onto NavigationController but keep the TabBar?