PostgreSQL: How to Find the Size of an Array

Often in PostgreSQL you may want to find the size of an array.

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

SELECT *
       cardinality(positions) AS positions_length
FROM athletes;

This particular example will find the size of each array in the positions column of the table and save the results to a new column named positions_length.

Note that we use the cardinality command to count the number of elements in an array.

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

Example: How to Find Size of an 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', '{"Guard"}');
INSERT INTO athletes VALUES (0005, 'Celtics', '{"Guard", "Center"}');
INSERT INTO athletes VALUES (0006, 'Nets', '{"Center", "Guard", "Guard"}');

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

Suppose that we would like to find the size of each array in the positions column of the table.

We can use the following syntax to do so:

SELECT *
       cardinality(positions) AS positions_length
FROM athletes;

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

Notice that the resulting table only contains a new column named positions_length that shows the number of elements in the corresponding array in the positions column.

Feel free to use similar syntax to find the size of arrays in your own table.

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 Check if Array Field Contains Value
PostgreSQL: Check if Array Contains Any of Multiple Values