PostgreSQL: Check if Array Contains Any of Multiple Values

Often in PostgreSQL you may want to check if an array field in a table contains any of multiple values.

You can use the following syntax to do so:

SELECT *
FROM athletes
WHERE positions && ARRAY['Center', 'Forward'];

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

Note that we use the && operator along with the ARRAY command to check if there is any overlap between the values in the positions array and the values specified in the ARRAY list.

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

Example: Check if Array Field Contains Any of Multiple Values 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", "Guard"}');
INSERT INTO athletes VALUES (0003, 'Kings', '{"Guard", "Forward"}');
INSERT INTO athletes VALUES (0004, 'Lakers', '{"Guard", "Guard"}');
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,Guard}
  3 | Kings   | {Guard,Forward}
  4 | Lakers  | {Guard,Guard}
  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 either ‘Center’ or ‘Forward’ as one of the values.

We can use the following syntax to do so:

SELECT *
FROM athletes
WHERE positions && ARRAY['Center', 'Forward'];

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

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

Note: Feel free to include as many values as you’d like to check for in the ARRAY list.

Additional Resources

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

PostgreSQL: How to Calculate Sum by Group
PostgreSQL: How to Check if Array is Empty
PostgreSQL: How to Check if Array Field Contains Value