Postgres: check if array field contains value? – Postgresql

The Problem:

In a Postgres database, there is a table with a column named ‘pub_types’ that stores an array of strings. The objective is to retrieve all the rows from the table where the ‘pub_types’ array contains a specific value, such as ‘Journal’. Despite searching and trying various methods, the user has not found a straightforward solution to check if the array field contains the desired value.

The Solutions:

Solution 1: Using ANY Operator

To check if an array field contains a specific value in PostgreSQL, you can use the ANY operator. The syntax is as follows:

<value> = ANY (<array>)

In your case, to find all the rows with “Journal” in the pub_types array, you would use the following query:

select * from mytable where 'Journal' = ANY(pub_types);

This query will return all the rows where the pub_types array contains the value “Journal”.

Note that the value you are searching for must be enclosed in single quotes, as it is a string literal. Additionally, the ANY operator is case-sensitive, so if you are searching for a value that is case-insensitive, you should use the ILIKE operator instead.

Solution 2: Use Array Comparison Operators

PostgreSQL provides several array comparison operators that allow you to check if an array field contains a specific value or set of values. Here are some commonly used operators:

• ANY: The ANY operator checks if at least one element in the array matches the specified value. Syntax: WHERE array_field ANY (<value>)
• ALL: The ALL operator checks if all elements in the array match the specified value. Syntax: WHERE array_field ALL (<value>)
• @>: The @> operator checks if the array contains all the values in the specified array. Syntax: WHERE array_field @> (<array_of_values>)
• <<@: The <<@ operator checks if the array contains any of the values in the specified array. Syntax: WHERE array_field <<@ (<array_of_values>)

For example, to find all rows in the mytable where pub_types contains the value "Journal", you can use the following query:

<!– language: sql –>

SELECT * FROM mytable WHERE pub_types @> ARRAY['Journal'];

This query uses the @> operator to check if the pub_types array contains the value "Journal".

Solution 3: Using array_to_string() method

Using the Postgres array_to_string() method, we can match on 'Journal' appearing as part of a string in the array:

select * from mytable where array_to_string(pub_types, ',') like '%Journal%';

If you want to match only the exact word 'Journal', remove the wildcards:

select * from mytable where array_to_string(pub_types, ',') = 'Journal';

Here’s the reference documentation for array_to_string():

https://www.postgresql.org/docs/9.1/functions-array.html