To find the position of occurrence of an element in an array we use array_position() and array_positions() functions. The common functionality of these functions is that they take an array and an element as arguments and return the index of where that element(specified in the array) has occurred in the array.

Let’s see how both of these functions particularly work.

PostgreSQL array_position() Vs. array_positions() - What's the Difference?

As discussed above, the common functionality of both functions is that they both give the index of occurrence of an element in an array that is passed in the function as arguments. However, there is a difference between the functionality of both functions.

First, let’s discuss the functionality of array_position().

PostgreSQL array_position() Function

The array_position() function in PostgreSQL takes an array and an element as arguments and returns the index of the first occurrence of that element in the array. The basic syntax for the function is given as:

array_position(Array, Element[, Start index]);

The array and the element are required arguments, the array is the first argument where the search will take place and the second argument is an element that is to be searched. The optional third argument specifies the index from where the function starts its search from.

Note that if the element is missing in that array, the function returns NULL.

Let's consider the below example to make it more clear.

Example

To understand how the array_position() function works, consider the example below.

SELECT array_position(ARRAY['John', 'Sarah', 'Peter', 'Alex', 'Williams', 'Sarah'], 'Sarah');

In the above query, we have passed an array i.e. ['John', 'Sarah', 'Peter', 'Alex', 'Williams', 'Sarah'] and we are finding an element 'Sarah' in it. This will return the position or the index where the element 'Sarah' is located first in the array.

We can clearly see that the element 'Sarah' is located at the 2nd position first in the array. Note that the element is again repeated at position 6 but is not returned by the query because array_position() returns the index number where that element occurred first.

Now we will intentionally search for an element that is not present in the array to check the output. The query will be:

We can see that the query has returned NULL because it can not find the element in the array.

Consider another query as an example to demonstrate how the third argument will work in searching:

SELECT array_position(ARRAY['John', 'Sarah', 'Peter', 'Alex', 'Williams', 'Sarah'], 'Sarah', 3 );

• The array is ['John', 'Sarah', 'Peter', 'Alex', 'Williams', 'Sarah']
• The element to be searched is ‘Sarah’.
• The third element indicated that you have to search for the element after that index position. This means if that element has occurred at a position before that specified index that will not count.

Let's see the output so it becomes more clear:

Now here you can see that the element ‘Sarah’ has appeared at index numbers 2 and 6 but the third argument is 3 which means that you have to look for the element ‘Sarah’ after index number 3. The query will return that index which represents the occurrence of ‘Sarah’ after index 3. That is why the query has returned 6.

So this is how the array_position() function works. We will move towards array_positions() next.

PostgreSQL array_positions() Function

The array_positions() function in PostgreSQL takes an array and an element as arguments and returns the index of all occurrences of that element in the array. The basic syntax for the function is given as:

array_positions(Array, Element);

The array and the element are required arguments, the array is the first argument where the search will take place and the second argument is an element that is to be searched. The query will return all those indexes where the searched element has occurred. If the element is missing in that array, the function returns NULL.

Let's consider the below example to make it more clear.

Example

To understand the concept of array_positions() more clearly, consider the query given below:

SELECT array_positions(ARRAY['John', 'Sarah', 'Peter', 'Alex', 'Williams', 'Sarah'], 'Sarah');

In the above query:

• The array is ['John', 'Sarah', 'Peter', 'Alex', 'Williams', 'Sarah']
• The element to be searched is ‘Sarah’.

Now the output of the above query is all the index positions where the element has occurred. The output is given below:

The element “Sarah” has occurred twice, first at index 2 position and second at index 6 position. The working of array_positions() is quite simple.

This is all about the working of both array_position() and array_positions() functions.

Conclusion

The difference between the Array_position() function and the Array_positions() function is that the Array_position() function gives the first occurrence of an element while the Array_positions() function gives all the occurrences of an element in an array. Both functions find the position of any element in an array. These functions take the array and the element as arguments and return the index of occurrence.