PostgreSQL: How to Check if Array Field Contains Value

Often in PostgreSQL you may want to check if an array field in a table contains a specific value.

You can use the following syntax to do so:

SELECT *
FROM athletes
WHERE 'Forward'=ANY(positions);

This particular example will return only the rows in the athletes table where the array field named positions contains ‘Forward’ as one of the values.

Note that we use the ANY command within the WHERE clause to check if any of the values in the positions field are equal to the string that we specified.

The following example shows how to use this syntax in practice.

Example: Check if Array Field Contains Value in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball teams:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER PRIMARY KEY,
  team TEXT,
  positions TEXT[]
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', '{"Guard", "Forward"}');
INSERT INTO athletes VALUES (0002, 'Hawks', '{"Guard", "Center"}');
INSERT INTO athletes VALUES (0003, 'Kings', '{"Guard", "Forward"}');
INSERT INTO athletes VALUES (0004, 'Lakers', '{"Forward", "Forward"}');
INSERT INTO athletes VALUES (0005, 'Celtics', '{"Guard", "Center"}');
INSERT INTO athletes VALUES (0006, 'Nuggets', '{"Guard", "Center"}');

-- view all rows in table
SELECT * FROM athletes;

 id |  team   |     positions     
----+---------+-------------------
  1 | Mavs    | {Guard,Forward}
  2 | Hawks   | {Guard,Center}
  3 | Kings   | {Guard,Forward}
  4 | Lakers  | {Forward,Forward}
  5 | Celtics | {Guard,Center}
  6 | Nuggets | {Guard,Center}

Suppose that we would like to only return the rows where the array field named positions contains the string ‘Forward’ as one of the values.

We can use the following syntax to do so:

SELECT *
FROM athletes
WHERE 'Forward'=ANY(positions);

 id |  team  |     positions     
----+--------+-------------------
  1 | Mavs   | {Guard,Forward}
  3 | Kings  | {Guard,Forward}
  4 | Lakers | {Forward,Forward}

Notice that the resulting table only contains the rows where the positions field contains ‘Forward’ as one of the values.

Feel free to replace ‘Forward’ with any other value that you would like to check for instead.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Extract Substring Before Specific Character
PostgreSQL: How to Extract Substring Between Two Characters
PostgreSQL: How to Extract Date from a Timestamp