PostgreSQL: Select Rows where Value NOT in Array

Often in PostgreSQL you may want to select rows from a table where a value does NOT appear in an array field.

You can use the following syntax to do so:

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

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

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

Example: Select Rows Where Value NOT in Array 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 does not contain the string ‘Forward’ as one of the values.

We can use the following syntax to do so:

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

 id |  team   |   positions    
----+---------+----------------
  2 | Hawks   | {Guard,Center}
  5 | Celtics | {Guard,Center}
  6 | Nuggets | {Guard,Center}

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

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

Additional Resources

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

PostgreSQL: How to Check if Array is Empty
PostgreSQL: How to Find the Size of an Array
PostgreSQL: How to Check if Array Field Contains Value