How to Check if PostgreSQL Array Contains Value

Sometimes you may need to check if value exists in PostgreSQL array. In this article we will look at how to check if PostgreSQL array contains value.

How to Check if PostgreSQL Array Contains Value

It is very easy to check if a value is present in a PostgreSQL array, using ANY function.

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.

Also read : How to Update Array in PostgreSQL

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

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}

Also read : How to Insert into Array in PostgreSQL

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}

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.