PostgreSQL makes it very easy to compare arrays of values using simple operators. 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 (&&).
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.
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
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.
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.
#to check if elements of array['b', 'c', 'd'] contain elements of array['b', 'c'] postgres=# select array['b', 'c', 'd'] @> array['b', 'c'] as contains; contains ---------- t # to check if array[1, 2, 4] is contained by array[4, 5, 2, 1] postgres=#select array[1, 2, 4] <@ array[4, 5, 2, 1] as is_contained_by; is_contained_by ----------------- t
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
Need a reporting tool for PostgreSQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
