PostgreSQL: How to Check if Array is Empty

Often in PostgreSQL you may want to check if an array is empty.

You can use the array_length command with the following syntax to do so:

SELECT *
FROM athletes
WHERE array_length(positions, 1) > 0;

This particular example will return only the rows in the athletes table where the array field named positions is not empty.

Note that we use the array_length command (with the argument 1 to specify that the array has 1 dimension) to retrieve the length of the array and place it in the WHERE clause to only return rows where the length of the array is greater than zero, i.e. not empty.

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

Example: How to Check if Array is Empty 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', '{}');
INSERT INTO athletes VALUES (0005, 'Celtics', '{"Guard", "Center"}');
INSERT INTO athletes VALUES (0006, 'Nuggets', '{}');

-- 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  | {}
  5 | Celtics | {Guard,Center}
  6 | Nuggets | {}

Suppose that we would like to only return the rows where the array field named positions is not empty.

We can use the following syntax to do so:

SELECT *
FROM athletes
WHERE array_length(positions, 1) > 0;

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

Notice that the resulting table only contains the rows where the positions field is not empty.

Feel free to use similar syntax to filter your own table for rows where an array field is not empty.

Additional Resources

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

PostgreSQL: How to Check if Array Field Contains Value
PostgreSQL: How to Calculate Sum of Multiple Columns
PostgreSQL: How to Calculate Sum by Group