How To Compare Arrays in PostgreSQL

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!

mm

About Ubiq

Ubiq is a dashboard & reporting platform for small & medium businesses. Build dashboards, charts & reports for your business in minutes. Try it for free today!