Searching in integer Arrays in postgres

SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

In addition, you can find rows where the array has all values equal to 10000 with:

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

Alter natively, the generate_subscripts function can be used. For example

 SELECT * FROM
   (SELECT pay_by_quarter,
           generate_subscripts(pay_by_quarter, 1) AS s
      FROM sal_emp) AS foo
 WHERE pay_by_quarter[s] = 10000;

Real time example:

Structure 

contactids, companyids,added_by_search_contactids,added_by_search_companyids => integer array  

1. SELECT * FROM  (SELECT contactids,generate_subscripts(contactids, 1) AS s 

   FROM lists limit 50000 ) AS foo 

   WHERE contactids[s]  IN (select id from contacts where current_details_id IS NULL); 

2. UPDATE lists SET contactids = coalesce(contactids,ARRAY[]::INT[]) - coalesce(ARRAY(select id 

   from contacts where current_details_id IS NULL),ARRAY[]::INT[]),

   added_by_search_contactids = coalesce(added_by_search_contactids,ARRAY[]::INT[]) - 

   coalesce(ARRAY(select id from contacts where current_details_id IS NULL),ARRAY[]::INT[]),

   companyids = coalesce(companyids,ARRAY[]::INT[]) - coalesce(ARRAY(select id from

   companies where current_details_id IS NULL),ARRAY[]::INT[]),

   added_by_search_companyids = coalesce(added_by_search_companyids,ARRAY[]::INT[]) - 

   coalesce(ARRAY(select id from companies where current_details_id IS NULL),ARRAY[]::INT[]) 

   WHERE id IN (SELECT id FROM (SELECT id,contactids,generate_subscripts(contactids, 1)AS s FROM lists

   WHERE current_step >= 4 AND folder_id IS NOT NULL and is_deleted IS FALSE LIMIT 20000 OFFSET 80000) AS foo 

   WHERE contactids[s] = ANY(select id from contacts where current_details_id IS NULL) UNION SELECT id 

   FROM (SELECT id,added_by_search_contactids,generate_subscripts(added_by_search_contactids, 1)AS s FROM lists WHERE 

   current_step >= 4 AND folder_id IS NOT NULL and is_deleted IS FALSE LIMIT 20000 OFFSET 80000) AS foo 

   WHERE added_by_search_contactids[s] = ANY(select id from contacts where current_details_id IS NULL) 

   UNION SELECT id FROM (SELECT id,companyids,generate_subscripts(companyids, 1)AS s FROM lists 

   WHERE current_step >= 4 AND folder_id IS NOT NULL and is_deleted IS FALSE LIMIT 20000 OFFSET 80000) AS foo WHERE 

   companyids[s] = ANY(select id from companies where current_details_id IS NULL) UNION SELECT id FROM 

   (SELECT id,added_by_search_companyids,generate_subscripts(added_by_search_companyids, 1)AS s FROM lists 

   WHERE current_step >= 4 AND folder_id IS NOT NULL and is_deleted IS FALSE LIMIT 20000 OFFSET 80000) AS foo 

   WHERE added_by_search_companyids[s] = ANY(select id from companies where current_details_id IS NULL)) 

  RETURNING lists.id

More information here

http://www.postgresql.org/docs/current/static/arrays.html
http://www.postgresql.org/docs/current/static/functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS.

If you have  any quires please post your comments here.