PostgreSQL: How to Find Position of Value in Array

Often in PostgreSQL you may want to find the position of a specific value in an array.

You can use the array_position() function with the following syntax to do so:

SELECT *,
       array_position(roles, 'Forward') AS forward_position
FROM athletes;

This particular example finds the position of the value ‘Forward’ in the array column named roles in the athletes table.

Note that this will return the position of the first occurrence of ‘Forward’ in the array. If you would like to return the position of all occurrences, then use the array_positions() function with the same syntax.

The following example shows how to use this syntax in practice.

Example: How to Find Position of Value in Array in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball teams:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER PRIMARY KEY,
  team TEXT,
  roles TEXT[]
);

-- add rows to the table 
INSERT INTO athletes VALUES (1, 'Mavs', '{"Guard", "Forward", "Center"}');
INSERT INTO athletes VALUES (2, 'Hawks', '{"Guard", "Center", "Center"}');
INSERT INTO athletes VALUES (3, 'Kings', '{"Guard", "Center", "Forward"}');
INSERT INTO athletes VALUES (4, 'Lakers', '{"Forward", "Forward", "Guard"}');
INSERT INTO athletes VALUES (5, 'Celtics', '{"Guard", "Forward", "Center"}');
INSERT INTO athletes VALUES (6, 'Nuggets', '{"Forward", "Guard", "Center"}');

-- view all rows in table
SELECT * FROM athletes;

 id |  team   |          roles          
----+---------+-------------------------
  1 | Mavs    | {Guard,Forward,Center}
  2 | Hawks   | {Guard,Center,Center}
  3 | Kings   | {Guard,Center,Forward}
  4 | Lakers  | {Forward,Forward,Guard}
  5 | Celtics | {Guard,Forward,Center}
  6 | Nuggets | {Forward,Guard,Center}

Suppose that we would like to find the position of the value ‘Forward’ in each array in the roles column.

We can use the following syntax to do so:

SELECT *,
       array_position(roles, 'Forward') AS forward_position
FROM athletes;

 id |  team   |          roles          | forward_position 
----+---------+-------------------------+------------------
  1 | Mavs    | {Guard,Forward,Center}  |                2
  2 | Hawks   | {Guard,Center,Center}   |                 
  3 | Kings   | {Guard,Center,Forward}  |                3
  4 | Lakers  | {Forward,Forward,Guard} |                1
  5 | Celtics | {Guard,Forward,Center}  |                2
  6 | Nuggets | {Forward,Guard,Center}  |                1

Notice that the resulting table contains a new column named forward_position that shows the position of the value ‘Forward’ in each array in the roles column.

For example, we can see:

• ‘Forward’ occurs in position 2 of the array in the first row.
• ‘Forward’ does not occur at all in the array in the second row.
• ‘Forward’ occurs in position 3 of the array in the third row.

And so on.

You can use similar syntax to find the position of a specific value in an array column in your own table.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Check if Array is Empty
PostgreSQL: How to Find the Size of an Array
PostgreSQL: How to Check if Array Field Contains Value
PostgreSQL: How to Append Item to Array Column