In PostgreSQL, the comparison, containment, and overlap operators are used to compare arrays. The comparison operators are further categorized into two categories: the equality operators and the ordering operators. All these operators serve unique functionality, for instance, the equality operators perform the element-by-element comparison, the containment operators check if an array is contained by some other array or not, etc.

This post explains the following methods to compare arrays in Postgres:

• Method 1: Using Comparison Operators
• Method 2: Using Containment Operators
• Method 3: Using Overlap Operator

Method 1: Using Comparison Operators

The comparison operators are of two types, i.e., equality operators, and ordering operators:

- The equality operators perform the element-by-element comparison and retrieve a boolean true or false.
- The “true” value signifies that the arrays are equal while the “false” value represents that the arrays are not equal.
- The equality operators include an equal to operator “=” and a not equal to operator “!=”, or “<>”.
- The ordering operators perform the comparison based on the array’s order.
- The ordering operators include a greater than sign “>”, a less than sign “<”, a greater than or equal to “>=” sign, and a less than or equal to “<=” sign.
- The ordering operators retrieve the result based on the first distinct pair of elements.

Example 1: How to Perform Array Comparison Using Equality Operators?

Use the “=” operator to test if the given arrays are equal or not:

SELECT ARRAY['John', 'Joseph', 'Anna', 'Henry'] = ARRAY['Henry', 'John'] As is_equal;

The “=” operator performs the exact element-by-element comparison and returns “f” because the elements of the given arrays are not equal. Let’s replace the “=” with “<>” operator and see how it works:

SELECT ARRAY['John', 'Joseph', 'Anna', 'Henry'] <> ARRAY['Henry', 'John'] As not_equal;

The boolean “t” in the output shows that the input arrays are not equal.

Example 2: How to Perform Array Comparison Using Ordering Operators?

The following example demonstrates the working of Postgres ordering operators:

SELECT ARRAY['John', 'Joseph', 'Anna', 'Henry'] > ARRAY['Henry', 'John'] As greater_than,
ARRAY['John', 'Joseph', 'Anna', 'Henry'] < ARRAY['Henry', 'John'] As less_than,
ARRAY['John', 'Joseph', 'Anna', 'Henry'] >= ARRAY['Henry', 'John'] As greater_than_equal_to,
ARRAY['John', 'Joseph', 'Anna', 'Henry'] <= ARRAY['Henry', 'John'] As less_than_equal_to;

The output snippet shows the comparative analysis of the ordering operators.

Method 2: Using Containment Operators

In Postgres, the containment operators check if one array contains the elements of some other array or not. The containment operators include a “@>” operator and a “<@” operator. The “ @>” operator checks if the right array is contained by the left array. While the “<@” operator checks if the left array is contained by the right array.

Example: How to Compare Arrays Using Containment Operators?

In the following example, the containment operator “<@” is used to compare two arrays:

SELECT ARRAY['John', 'Joseph', 'Anna', 'Henry'] <@ ARRAY['Henry', 'John'] As contained_by_right_arr;

The “<@” operator will check if all the elements of the left array are contained by the right array:

The “f” in the output shows that all the elements of the left array are not present in the right array. Now, use the “@>” operator to see if the elements of the right array are contained by the left array:

SELECT ARRAY['John', 'Joseph', 'Anna', 'Henry'] @> ARRAY['Henry', 'John'] As contained_by_left_arr;

The boolean “t” in the output represents that all elements of the right array are present in the left array.

Method 3: Using Overlap Operator

In PostgreSQL, the “&&” sign is referred to as the overlap operator. The “&&” operator is used to test if the given arrays have some common elements.

Example: How to Compare Arrays Using Overlap Operators?

In the below example, the overlap operator is used to compare the given arrays:

SELECT ARRAY['John', 'Joseph', 'Anna', 'Henry'] && ARRAY['Henry', 'John'] As overlapped_array;

The output signifies that the given arrays are overlapped(contain common elements).

Conclusion

To compare arrays in PostgreSQL, the equality, ordering, containment, and overlap operators are used. The equality operators perform the element-by-element comparison, the ordering operators perform the comparison based on the array’s order, the containment operators check if an array is contained by some other array or not, while the overlap operator checks if the given arrays have some common elements. This post explained various methods to compare arrays in Postgres.