PostgreSQL has a couple of functions that enable us to get the position or positions of a given value in an array. One of them – array_position() – returns the position of just the first occurrence (as an integer), while the other function – array_positions() – returns the position of all occurrences (as an array).

So these functions are aptly named – one is singular and the other is plural.

But this isn’t the only difference. The array_position() function allows us to specify a start position, while array_positions() doesn’t.

Continue reading →

We can use the array_remove() function in PostgreSQL to remove elements from an array.

We pass two arguments when calling the function; the first is the array and the second argument is the element to remove. All occurrences of the specified element are removed.

The function returns an identical array, but without the specified element/s.

Continue reading →

In PostgreSQL, we can use the array_cat() function to concatenate two arrays.

We pass both arrays as arguments when we call the function.

Continue reading →

In PostgreSQL, the array_prepend() function prepends an element to the start of an array.

The first argument is the element to prepend, and the second argument is the array.

Continue reading →