How To Compare Arrays in PostgreSQL

Last updated on July 29th, 2024 at 06:08 am

PostgreSQL supports numerous data types including complex ones such as arrays. You easily create arrays, insert values into arrays, update arrays and even check if array contains a value. Often database programmers and web developers need to compare arrays in PostgreSQL. It can be tedious to perform such a comparison from scratch. Luckily PostgreSQL provides several straight forward operators for this purpose. In this article, we will look at how to compare arrays in PostgreSQL database.

How To Compare Arrays in PostgreSQL

PostgreSQL allows you to compare arrays using equality operators (=, <>), ordering operators (>, <, >=, <=), containment operators (@>, <@) and overlap operators (&&). Each of these operators has a different purpose.

However, in case of each of these operators, after PostgreSQL compares arrays, it returns t for true or f for false as result.

Let us look at each of these operators one by one.

Also read : How to Change user password in PostgreSQL

Compare Arrays for Equality

Equality operators do an element-by-element comparison to check if the two arrays are equal or not. ‘=’ operator is used to check if two arrays are equal whereas ‘<>’ operator is used to check if two arrays are not equal.

Here are sample SQL queries to compare two arrays using equality operators. The following two arrays are not equal.

postgres=# select array[2,3,4] = array[1,2,5] as compare;
 compare
---------
 f

postgres=# select array[2,3,4] <> array[1,2,5] as compare;
 compare
---------
 t

In the first query, we check if two arrays are equal, which returns false. In second query, if the same two arrays are not equal, which returns true.

Also read : How to Create User with Superuser Privileges

Compare Arrays for Order

Ordering operators (>, <, >=, <=) do an element-by-element comparison between two arrays to check if each pair of elements follow the required order condition. If there is no mismatch it returns t, else f. Please note, the comparison result is not based on size of arrays but on the first different pair of elements.

Here is an example

postgres=# select
           array[1,3,5] >= array[1,3,4] as compare1,
           array[1,3,5] <= array[1,3,4,5] as compare2;
 compare1 | compare2
----------+----------
 t        | f

In the first comparison, each element of first array is >= each element of second array. Hence, you get true. In second comparison, the first array does not have 4th element, required for comparison with 4th element of second array. Therefore, comparison fails and you get false as a result.

Also read : How to List All Users in PostgreSQL

Compare Arrays for Containment

Containment operators (<@, @>) allow you to check if one array contains another array using @> operator, or if one array is contained by another array using <@ operator. @> operator checks if left array contains right array whereas <@ checks if right array contains left array.

postgres=# select array['b', 'c', 'd'] @> array['b', 'c'] as contains;
 contains
----------
 t

postgres=# select array[1, 2, 4] <@ array[4, 5, 2, 1] as is_contained_by;
 is_contained_by
-----------------
 t

In the first example, we check if elements of array[‘b’, ‘c’, ‘d’] contain elements of array[‘b’, ‘c’]. In the second example, we check if array[1, 2, 4] is contained by array[4, 5, 2, 1].

Also read : How to Check PostgreSQL version

Compare Arrays for Overlap

Overlap operator (&&) lets you check if there are any common elements in two arrays.

postgres=# select
postgres-# array[2, 4] && array[4, 3] as overlap1,
postgres-# array[0, 2] && array[1, 4] as overlap2;
 overlap1 | overlap2
----------+----------
 t        | f

Conclusion

In this article, we have learnt several simple ways to quickly compare PostgreSQL arrays. The equality operator checks if two arrays are equal or not, ordering operators compare the order of items in two arrays, containment operator checks if one array is present in another, and overlap operator checks if the arrays overlap each other. Depending on your requirement, you can use the appropriate operator. Without them, it would have been really difficult to compare arrays.

Need a reporting tool for PostgreSQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!