How to Find Element in Array in PostgreSQL Quickly

In PostgreSQL, you can find if an element exists in an array using the ANY or ARRAY_POSITION functions. Use element = ANY(array_column) to check presence or ARRAY_POSITION(array_column, element) to get the index of the element in the array.

Syntax

To check if an element exists in an array, use:

• element = ANY(array_column): Returns true if the element is found in the array.
• ARRAY_POSITION(array_column, element): Returns the 1-based index of the element in the array or NULL if not found.

sql

SELECT element = ANY(array_column) AS is_present
FROM (VALUES (ARRAY[1,2,3], 2), (ARRAY[4,5,6], 7)) AS t(array_column, element);

SELECT ARRAY_POSITION(ARRAY[1,2,3], 2) AS position;
SELECT ARRAY_POSITION(ARRAY[1,2,3], 5) AS position;

Output

 is_present 
------------
 t
 f

 position 
----------
        2

 position 
----------
     (null)

Example

This example shows how to find if the number 3 exists in an integer array column and how to get its position.

sql

CREATE TEMP TABLE example_table (id SERIAL, numbers INT[]);

INSERT INTO example_table (numbers) VALUES
  (ARRAY[1, 2, 3, 4]),
  (ARRAY[5, 6, 7]),
  (ARRAY[3, 8, 9]);

-- Check if 3 is in the array
SELECT id, numbers, 3 = ANY(numbers) AS has_three
FROM example_table;

-- Get position of 3 in the array
SELECT id, numbers, ARRAY_POSITION(numbers, 3) AS position_of_three
FROM example_table;

Output

 id |   numbers    | has_three 
----+--------------+-----------
  1 | {1,2,3,4}    | t         
  2 | {5,6,7}      | f         
  3 | {3,8,9}      | t         

 id |   numbers    | position_of_three 
----+--------------+-------------------
  1 | {1,2,3,4}    |                 3 
  2 | {5,6,7}      |              (null)
  3 | {3,8,9}      |                 1

Common Pitfalls

One common mistake is using IN to check for an element inside an array, which does not work as expected because IN compares scalar values, not arrays.

Also, remember that ARRAY_POSITION returns NULL if the element is not found, so always handle NULL results properly.

sql

/* Wrong: This does not check if 3 is in the array */
SELECT * FROM example_table WHERE 3 IN (numbers);

/* Right: Use ANY to check element in array */
SELECT * FROM example_table WHERE 3 = ANY(numbers);

Output

ERROR:  operator does not exist: integer = integer[]
LINE 1: SELECT * FROM example_table WHERE 3 IN (numbers);
                                         ^

 id |   numbers    
----+--------------
  1 | {1,2,3,4}    
  3 | {3,8,9}

Quick Reference

Function/Operator	Purpose	Returns
element = ANY(array)	Check if element exists in array	Boolean (true/false)
ARRAY_POSITION(array, element)	Find position of element in array	Integer index or NULL
array @> ARRAY[element]	Check if array contains element(s)	Boolean (true/false)

Key Takeaways

Use element = ANY(array) to check if an element exists in a PostgreSQL array.

Use ARRAY_POSITION(array, element) to find the index of an element in the array.

Avoid using IN to check elements inside arrays; it does not work as expected.

Remember ARRAY_POSITION returns NULL if the element is not found.

You can also use the @> operator to check if an array contains specific elements.