PostgreSQL: Find Position of Element in Array

When you’re working with arrays in PostgreSQL, you often need to know where a specific element is located. Maybe you’re checking if a skill appears at the beginning or end of a list, or you need to identify its position for ordering purposes.

Quick Answer: Use ARRAY_POSITION

To find where an element appears in an array, use the ARRAY_POSITION function:

SELECT
    emp_id,
    first_name,
    skills,
    ARRAY_POSITION(skills, 'SQL') as position_of_sql,
    ARRAY_POSITION(skills, 'Python') as position_of_python
FROM employees
WHERE skills IS NOT NULL
LIMIT 5;

Returns:

emp_id | first_name | skills              | position_of_sql | position_of_python
-------+------------+---------------------+-----------------+-------------------
     1 | James      | {Python,Java,SQL}   |               3 |                  1
     2 | Mary       | {Java,C++,Go}       |                 |
     3 | David      | {SQL,Python,Java}   |               1 |                  2

That’s it. When the element isn’t found, it returns NULL.

How It Works

ARRAY_POSITION(array, element) scans through the array and returns the 1-based position of the first matching element. Arrays in PostgreSQL are 1-indexed (first element is position 1), so if ‘SQL’ is the third item, you get 3.

If the element doesn’t exist in the array, the function returns NULL — not an error. This makes it safe to use in WHERE clauses and conditional logic.

Different Approaches & When to Use Them

Approach 1: Single Element Lookup (Simplest)

Use ARRAY_POSITION when you just need to find one element:

SELECT
    emp_id,
    skill_position
FROM employees
WHERE ARRAY_POSITION(skills, 'JavaScript') IS NOT NULL
ORDER BY emp_id;

This is the fastest and cleanest approach for most use cases.

Best for: Finding if an element exists and where it is.

Approach 2: Conditional Logic Based on Position

Check where an element appears and branch your logic:

SELECT
    emp_id,
    first_name,
    CASE
        WHEN ARRAY_POSITION(skills, 'Python') IS NULL
        THEN 'No Python'
        WHEN ARRAY_POSITION(skills, 'Python') = 1
        THEN 'Python is top skill'
        WHEN ARRAY_POSITION(skills, 'Python') <= 3
        THEN 'Python is in top 3'
        ELSE 'Python is listed but not top priority'
    END as python_proficiency_level
FROM employees
WHERE skills IS NOT NULL;

Best for: Categorizing records based on skill ordering.

Approach 3: Find All Positions (Multiple Occurrences)

If your array contains duplicates and you need all positions where an element appears, use UNNEST with ROW_NUMBER:

SELECT
    emp_id,
    first_name,
    skills,
    array_agg(position) as all_positions_of_java
FROM (
    SELECT
        emp_id,
        first_name,
        skills,
        ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY ordinality) as position
    FROM employees,
    LATERAL UNNEST(skills) WITH ORDINALITY AS t(skill, ordinality)
    WHERE skill = 'Java'
) sub
GROUP BY emp_id, first_name, skills;

Best for: When you need multiple matches, like if ‘Java’ appears twice in the array.

Real-World Example: Track Skill Adoption Patterns

Find which skills new employees learn first vs. last, and use position to measure adoption pace:

SELECT
    emp_id,
    first_name,
    skills,
    ARRAY_POSITION(skills, 'Docker') as docker_position,
    CASE
        WHEN ARRAY_POSITION(skills, 'Docker') IS NULL
        THEN 'Not yet adopted'
        WHEN ARRAY_POSITION(skills, 'Docker') = 1
        THEN 'Early adopter (learned first)'
        WHEN ARRAY_POSITION(skills, 'Docker') <= 3
        THEN 'Early phase (top 3)'
        ELSE 'Later phase'
    END as adoption_timeline
FROM employees
WHERE skills IS NOT NULL
ORDER BY ARRAY_POSITION(skills, 'Docker') NULLS LAST;

This helps you understand skill adoption patterns across your team. You can then target training for lagging skills.

Performance Comparison

Method Speed Use Case
ARRAY_POSITION Very Fast Single occurrence
UNNEST + ROW_NUMBER Medium Multiple matches
LATERAL with ordinality Medium Complex position logic

ARRAY_POSITION is optimized for this single task, so it’s fastest for basic lookups.

Decision Guide: Which Method to Use?

Scenario Use This Why
Find position of one element ARRAY_POSITION Simplest, fastest
Check if element exists ARRAY_POSITION with IS NOT NULL Direct answer
Find all positions of element UNNEST + ROW_NUMBER Needed for duplicates
Categorize based on position CASE + ARRAY_POSITION Flexible branching
Find last occurrence Custom query ARRAY_POSITION only finds first

FAQ

Q: What if the element doesn’t exist in the array? A: ARRAY_POSITION returns NULL. Check with IS NOT NULL in WHERE clause, or handle it in CASE statements.

Q: Can I find the LAST occurrence instead of first? A: ARRAY_POSITION only finds the first. To find last: ARRAY_LENGTH(array, 1) - ARRAY_POSITION(REVERSE(array), element) + 1 (though this is complex).

Q: How do I find ALL positions of duplicates? A: Use UNNEST with WITH ORDINALITY, then filter by the element. See Approach 3 above.

Q: Is ARRAY_POSITION case-sensitive? A: Yes. Use UPPER() for case-insensitive comparison: ARRAY_POSITION(array, UPPER('sql')).

Q: What about searching for NULL values? A: You can’t search for NULL with ARRAY_POSITION. Use NULL = ANY(array) instead.

Q: Performance on very large arrays? A: Still O(n) complexity, so linearly proportional to array size. But PostgreSQL’s optimizations make it very fast even for arrays with thousands of elements.

Wrapping Up

Finding element positions in arrays is straightforward with ARRAY_POSITION. For basic lookups, it’s your go-to function. For more complex scenarios like multiple matches or special conditions, combine it with UNNEST for flexibility.

The key takeaway: ARRAY_POSITION finds first occurrence and returns NULL if not found. Handle the NULL case properly in your logic, and you’re set.