ARRAY_FIND

Returns the ordinal position of a specified element in an array, or -1 if not found. The array may be empty but must not be NULL. This function uses null-safe equality checks when testing elements.

Behavior type

Syntax

ARRAY_FIND(array, val_to_find)

Arguments

array

Target array.

val_to_find

Value to search for; type must match or be coercible to the element type of the array.

Supported data types

Arrays of any dimensionality and element type.

Examples

=> SELECT array_find(array[1,2,3],2);
array_find
------------
          1
(1 row)

The function returns the first occurrence of the specified element. However, nothing ensures that value is unique in the array.

=> SELECT array_find(ARRAY[1,2,7,5,7],7);
 array_find
------------
          2
(1 row)

The function returns -1 if the specified element is not found.

=> SELECT array_find(ARRAY[1,3,5,7],4);
array_find
------------
        -1
(1 row)

You can search for complex element types:

=> SELECT ARRAY_FIND(ARRAY[ARRAY[1,2,3],ARRAY[1,null,4]], ARRAY[1,2,3]);
 ARRAY_FIND
------------
          0
(1 row)

=> SELECT ARRAY_FIND(ARRAY[ARRAY[1,2,3],ARRAY[1,null,4]], ARRAY[1,null,4]);
 ARRAY_FIND
------------
          1
(1 row)

The second example, comparing arrays with null elements, finds a match because ARRAY_FIND uses a null-safe equality check when evaluating elements.

As the previous examples show, the first argument can be a literal multi-dimensional array of primitive types. Literal arrays cannot contain ROW elements, as explained in Syntax for Direct Construction (Literals), but arrays in tables can. See CONTAINS for a related example.