Postgres provides a built-in function named ANY() that assists us in checking the existence of an element in an array. It accepts an array as an argument, checks the presence of the selected element, and returns a boolean true or false. The “true” value indicates that the selected value exists in the targeted array while the “false” value shows that the selected element doesn’t exist in the targeted array.

This write-up demonstrates how to check if an array contains a specific element or not.

How Do I Check if an Array Contains a Particular Value/Element?

Use the following syntax to check the existence of an element in an array via the ANY() function:

val = ANY (arr);

Here, the “val” represents a value to be checked in the targeted array, while “arr” represents the targeted array. The “arr” can be an array literal(enclosed within single quotes), a column of array data type, or a subquery that returns an array.

Example 1: Checking the Existence of a Text Value

The following code checks the presence of “Henry” in the given array using the ANY() function:

SELECT 'Henry' = ANY('{Seth, Mike, Joseph, Joe, John}') As array_contains;

The “f” in the output indicates the absence of “Henry” in the given array.

Example 2: Checking the Existence of a Numeric Value

The following code checks the existence of a value “25” in an array using the ANY() function:

SELECT 25 = ANY('{100, 12, 30, 150, 25}'::int[]) As array_contains;

The “::” operator is used in the code for string array to int array conversion:

The “t” represents that the selected element exists in the given array.

Example 3: How to Use ANY() Function on Table’s Data?

Let’s create a sample table and named it “std_tbl”:

CREATE TABLE std_tbl( 
std_id SMALLINT, 
std_name VARCHAR(15), 
marks_obtained INT[] 
);

Now, insert the data into the std_tble using the “INSERT INTO” command:

INSERT INTO std_tbl(std_id, std_name, marks_obtained)
VALUES (1, 'Joe', ARRAY[45, 57, 67, 70]),
(2, 'John', ARRAY[60, 45, 47, 77]),
(3, 'Stephen', ARRAY[55, 60, 70, 50]);

Let’s use the ANY() function to check the presence of “45” in the “marks_obtained” array:

SELECT std_name, marks_obtained
FROM std_tbl
WHERE 45 = ANY(marks_obtained) As array_contains;

The ANY() function returns all those records that contain a value of “45”.

Conclusion

PostgreSQL offers a built-in function named ANY() that checks the presence of a specific element in an array. It accepts an array as an argument, checks the presence of the given value in the array, and returns a boolean true or false. The “true” value represents that the selected value exists in the targeted array while the “false” value indicates that the selected element doesn’t exist in the targeted array. This guide presented a detailed guide on how to check the presence of a particular value in an array using the ANY() function.