Postgres Array Operators

Arrays are among the most advanced and standard data types in most programming languages and development environments. They provide extensive performance and versatility to store related data.

PostgreSQL database supports the array data types which allow you to store and manipulate a collection of values of a similar data type.

In this tutorial, we will explore the various array operators in PostgreSQL that allows us to perform the everyday array operations such as concatenation, containment checks, slicing, and more.

PostgreSQL Create Array

In PostgreSQL, we can create an array using the ARRAY[] constructor or the ARRAY keyword.

An example is shown in the following:

SELECT ARRAY[1, 2, 3] AS my_arr;

Output:

my_arr
---------
{1,2,3}
(1 row)

We can also create an array from the values of a given column. An example is as shown in the following:

select array(select id from wp_users) as user_ids;

The resulting array is as follows:

{1,2,3,4,5,6,7,8,9,10}

PostgreSQL Array Concatenation Operator

We can use the double pipe operator to join the values of two or more arrays in PostgreSQL.

An example query is as follows:

select ARRAY[1,2,3] || ARRAY[4,5,6,7]

This should return the values of the first array which is concatenated to the second array.

→ {1,2,3,4,5,6,7}

Concatenating a null or empty array is a no-op; otherwise, the arrays must have the same dimensions.

PostgreSQL Array Element Access

We can access the individual elements of an array using the subscript operator “[]”. The array index starts from 1. For example:

SELECT ARRAY[1, 2, 3][2] AS second_element;

Output:

PostgreSQL Array Containment

PostgreSQL provides the “@>” operator to check if one array contains all the elements of another. For example:

SELECT ARRAY[1, 2, 3] @> ARRAY[2, 3] AS contains;

The operator returns a Boolean value if the value is found in the array.

contains
----------
t
(1 row)

PostgreSQL Array Overlaps

The “&&” operator allows us to check whether two arrays have common elements. For example:

SELECT ARRAY[1, 2, 3] && ARRAY[3, 4, 5] AS overlaps;

Similarly, the operator returns a Boolean true if the input arrays have common elements.

overlaps
----------
t
(1 row)

PostgreSQL Array Intersection

PostgreSQL provides the “&&” operator to find the intersection of two arrays. The result is an array containing only the common elements. For example:

SELECT ARRAY[1, 2, 3] && ARRAY[3, 4, 5] AS intersection;

PostgreSQL Array Union

The “||” operator allows us to find the union of two arrays. The result is an array that contains all the elements from both collections without duplicates. For example:

SELECT ARRAY[1, 2, 3] || ARRAY[3, 4, 5] AS union;

Resulting Output:

union
---------------
{1,2,3,3,4,5}
(1 row)

PostgreSQL Array Difference

PostgreSQL also provides the “-“ operator to find the difference between two arrays. The result is an array that contains the elements from the first array that are not present in the second array. For example:

SELECT ARRAY[1, 2, 3] - ARRAY[2, 3, 4] AS difference;

PostgreSQL Array Aggregation

PostgreSQL also allows us to aggregate the values into an array using the “ARRAY_AGG” function. This function groups the values and returns an array that contains those values.

Conclusion

We explored the various array operators in PostgreSQL to perform various operations on an array.