How to Use array_position in PostgreSQL: Syntax and Examples

In PostgreSQL, use the array_position(array, element) function to find the first index of element in array. It returns the position as an integer or NULL if the element is not found.

Syntax

The array_position function takes two arguments:

• array: The array to search in.
• element: The value to find in the array.

It returns the 1-based index of the first occurrence of the element or NULL if not found.

sql

array_position(anyarray, anyelement) RETURNS integer

Example

This example shows how to find the position of the number 3 in an integer array and the position of the string 'apple' in a text array.

sql

SELECT array_position(ARRAY[1, 2, 3, 4, 3], 3) AS pos_number,
       array_position(ARRAY['banana', 'apple', 'orange'], 'apple') AS pos_string;

-- Example when element is not found
SELECT array_position(ARRAY[10, 20, 30], 40) AS pos_not_found;

Output

 pos_number | pos_string 
------------+------------
          3 |          2
(1 row)

 pos_not_found 
--------------
           NULL
(1 row)

Common Pitfalls

Common mistakes include:

• Expecting zero-based index: array_position returns 1-based positions.
• Not handling NULL when the element is missing.
• Using it on empty arrays returns NULL.

Example of wrong and right usage:

sql

-- Wrong: expecting zero-based index
SELECT array_position(ARRAY[5, 6, 7], 6) - 1 AS zero_based_pos;

-- Right: use as is or handle NULL
SELECT COALESCE(array_position(ARRAY[5, 6, 7], 6), 0) AS pos_or_zero;

Output

 zero_based_pos 
----------------
              2
(1 row)

 pos_or_zero 
------------
           2
(1 row)

Quick Reference

Function	Description	Returns
array_position(array, element)	Finds first index of element in array	Integer position (1-based) or NULL
Returns NULL if element not found	Use COALESCE to handle NULL	Custom default value
Works with any array type	Supports text, int, etc.	Integer or NULL

Key Takeaways

Use array_position(array, element) to get the first 1-based index of element in an array.

It returns NULL if the element is not found, so handle NULLs in your queries.

The function works with any array type like integer[], text[], etc.

Remember array_position indexes start at 1, not 0.

Use COALESCE to provide a default value when element is missing.