How to Check if PostgreSQL Array Contains Value

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

PostgreSQL is a popular database that supports a vast range of data types including arrays. While working array columns, database programmers and software developers may need to check if value exists in PostgreSQL array. It can be really difficult to implement this on your own without any available operators and functions. Luckily, there are several ways to do this in PostgreSQL. In this article we will look at how to check if PostgreSQL array contains value.

Table of Contents

How to Check if PostgreSQL Array Contains Value

It is very easy to check if a value is present in a PostgreSQL column when it is a string, text or varchar data type, but it can be tricky to do this in array. If you are a beginner database developer, you may end up using IN operator as shown below. But it only works with a set of strings, not an array or number or strings.

SELECT * FROM customers WHERE country IN ('India', 'US', 'UK');

Here are the different ways to update array in PostgreSQL. Let us say you have the following array column.

# create table employees ( 
     id int, 
     name varchar, 
     sales integer[] 
);

In the above example, we have created column sales as an array of integers.

Let us also insert data in our array column.

# insert into employees (id, name, sales)
          values (1, ' John Doe', ARRAY [9,7,5,4,3,2]),
                 (2, ' Jim Day', ARRAY [8,6,3,2,9,2]);

# select * from employees;
 id |   name    |     sales
----+-----------+---------------
  1 |  John Doe | {9,7,5,4,3,2}
  2 |  Jim Day  | {8,6,3,2,9,2}

There are several ways to find if an array contains a value. We will look at each of them.

1. Using ANY() Function

The ANY function allows you to quickly compare a column value with an array of other values. It returns true if there is a match, else it returns false. Here is the syntax of ANY function.

value = ANY (array)

In the above statement, you need to specify the value you want to check and the array in which you want to check its presence/absence. You can specify ANY function as a SELECT column or in WHERE clause.

You can specify the array as a literal using round braces and single quotes ( e.g ‘{1, 2, 3}’), a column name with array data type, or even a subquery whose result is an array.

The above statement will return t/f for True/False.

Here is a simple example of ANY statement.

postgres=# SELECT 1 = ANY ('{1,2,3}'::int[]);
 ?column?
----------
 t

postgres=# SELECT 4 = ANY ('{1,2,3}'::int[]);
 ?column?
----------
 f

The above examples use literal arrays. We can also use a column name to provide array values. Here is the SQL query to select only those rows where value 7 is present in array sales

# select * from employees where 7=ANY(sales);
 id |   name    |     sales
----+-----------+---------------
  1 |  John Doe | {9,7,5,4,3,2}

2. Using ALL Operator

On the other hand, if you want to check if a value is not present in an array, then you need to use ALL operator. Typically, database programmers end up using != operator to negate the ANY function but it will not work. For example, the following queries will not work.

SELECT 4 != ANY ('{1,2,3}'::int[]);
select * from employees where 7 != ANY(sales);

For this purpose, you will need to use ALL operator. It returns true only if all values in array meet the condition. The following queries will work.

SELECT 4 != ALL('{1,2,3}'::int[]);
select * from employees where 7 != ALL(sales);

3. Using Contains Operator

In all the above examples using ANY, you can only search for a single value in an array. What if you want to search for multiple values? In such cases, you can contains operator @>. This operator tells if you the first array contains second array. In fact, PostgreSQL provide many operators to compare arrays.

Here is an example.

SELECT * FROM mytable WHERE sales @> {7, 5};

In the above query, PostgreSQL will select only those rows where sales column contains both 7 and 5 values.

4. Using Overlap Operator

In the above example, PostgreSQL will check if all elements of one array are completely present in the other array. Sometimes you may need to check any of the elements in one array is present in another array. In other words, you may need to check if there is an overlap between two arrays. For such use cases, use the overlap operator &&.

SELECT * FROM mytable WHERE sales && {7, 5};

In the above query, PostgreSQL will return rows where any of the items 7 and 5 are present in sales column.

Conclusion

As you can see, PostgreSQL provides some really neat ways to easily work with array columns. Without them, it would have been very tedious to check if an array contains one or more strings. In this article, we have learnt how to check if a single value is present in an array, if one array contains another array, and if there is any overlap between two arrays. You can use any of them as per your requirements.

Check Value in Array Using Ubiq

Ubiq Reporting tool supports all the above SQL queries and makes it easy to visualize SQL results in different ways. Here is the SQL query mentioned above, in Ubiq. You can also plot SQL query results into charts & dashboards, and share them with others.