PostgreSQL Array: How to check if a value is present in a column having array of values

Question

I have a table in which one of the columns has array of values e.g. {apple, banana, candy, dog, elephant}. I want to select all those rows from the table where that column has either 'apple' or 'dog'. I tried IN clause, but it did not work. What is syntax to search an array in PostgreSQL?

Answer 1

IN clause may not work with array search in PostgreSQL. You can use '@>' or ANY or generate_subscripts to run SQL for array search. Here are 3 examples to show how to do array search in PostgreSQL. As generate_subscripts uses subquery, the 3rd approach can be too slow. I would recommend approach 1 or approach 2.

1. Using ANY

 SELECT X1.metavisit_occurrence_id, X1.concept_ids, X1.concept_set_name, X2.visit_types
FROM events_of_interest X1, metavisits_of_interest X2
WHERE X1.metavisit_occurrence_id  = X2.metavisit_occurrence_id
     AND ('inpatient' = ANY(X2.visit_types)  OR 'emergency' = ANY(X2.visit_types))

2. Using @> 

SELECT X1.metavisit_occurrence_id, X1.concept_ids, X1.concept_set_name, X2.visit_types
  FROM events_of_interest X1, metavisits_of_interest X2
  WHERE X1.metavisit_occurrence_id  = X2.metavisit_occurrence_id AND (X2.visit_types @> '{inpatient}' OR X2.visit_types @> '{emergency}')

3. Using generate_subscripts

    SELECT metavisit_occurrence_id, concept_ids, concept_set_name, visit_types
    FROM (
        SELECT X1.metavisit_occurrence_id, X1.concept_ids, X1.concept_set_name, X2.visit_types, generate_subscripts(X2.visit_types, 1) AS t
          FROM events_of_interest X1, metavisits_of_interest X2
          WHERE X1.metavisit_occurrence_id  = X2.metavisit_occurrence_id) foo
    WHERE foo.visit_types[t] = 'inpatient' OR foo.visit_types[t] = 'emergency'