Arrays and seriesPostgreSQL only

ARRAY_POSITION

Finds the number of the first array element with the given value.

Syntax

ARRAY_POSITION(<array>, <value>)
ARRAY_POSITIONS(<array>, <value>)

Returns the element number, counting from 1, or NULL if the value isn't there. ARRAY_POSITIONS returns the numbers of all matches as an array. Handy for finding out where a value sits in a list — for example, in a fixed order of statuses or priorities.

Data for the examples

employees

name	department	salary	bonus	phone
Emma	backend	1200	300	+44 7700 900123
Liam	backend	1000	NULL	NULL
Olivia	frontend	1350	150	+44 7700 900456
Noah	frontend	950	NULL	+44 7700 900789
Sophia	design	1200	200	NULL

These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.

In which position

SELECT ARRAY_POSITION(ARRAY['low', 'medium', 'high'], 'medium') AS medium_position,

ARRAY_POSITION(ARRAY['low', 'medium', 'high'], 'urgent') AS not_found;

medium_position	not_found
2	NULL

All matches

SELECT ARRAY_POSITIONS(ARRAY['a', 'b', 'a', 'c', 'a'], 'a') AS positions;

positions
[1,3,5]

Sorting in your own orderAdvanced

SELECT name, department

FROM employees

ORDER BY ARRAY_POSITION(ARRAY['design', 'frontend', 'backend'], department), name;

name	department
Sophia	design
Noah	frontend
Olivia	frontend
Emma	backend
Liam	backend

The departments aren't in alphabetical order but in the order the array sets.

Watch out

Not found means NULL, not 0 as with POSITION for strings. Check IS NULL.

Only for one-dimensional arrays: for ARRAY[[1, 2], [3, 4]] you'll get searching for elements in multidimensional arrays is not supported.

To simply check whether a value is in an array, 'medium' = ANY(<array>) is shorter.

Related functions

POSITION CARDINALITY UNNEST