Postgres array_length() function

The Postgres array_length() function is used to determine the length of an array along a specified dimension.

It's particularly useful when working with multi-dimensional arrays or when you need to perform operations based on the size of an array. Examples include data analysis where you might need to filter rows based on the number of elements in an array column. Another use case might be application development where you need to validate the size of array inputs since Postgres doesn't natively have a fixed-size array data type.

Function signature

The array_length() function has the following signature:

array_length(anyarray, int) -> int

• anyarray: The input array to measure.
• int: The array dimension to measure (1-based index).

Example usage

Consider a products table with a categories column that contains arrays of product categories. We can use array_length() to find out how many categories each product belongs to.

WITH products(product_name, categories) AS (
  VALUES
    ('Laptop', ARRAY['Electronics', 'Computers']),
    ('Coffee Maker', ARRAY['Appliances', 'Kitchen', 'Electronics']),
    ('Book', ARRAY['Books'])
)
SELECT
  product_name,
  categories,
  array_length(categories, 1) AS category_count
FROM products;

This query returns the product name, the array of categories it is listed in, and the count of categories for each product.

product_name |            categories            | category_count
--------------+----------------------------------+----------------
 Laptop       | {Electronics,Computers}          |              2
 Coffee Maker | {Appliances,Kitchen,Electronics} |              3
 Book         | {Books}                          |              1
(3 rows)

Advanced examples

Filter rows based on array length

You can use array_length() in a WHERE clause to filter rows based on the size of an array.

WITH orders(order_id, items) AS (
  VALUES
    (1, ARRAY['Shirt', 'Pants', 'Shoes']),
    (2, ARRAY['Book']),
    (3, ARRAY['Laptop', 'Mouse', 'Keyboard', 'Monitor'])
)
SELECT *
FROM orders
WHERE array_length(items, 1) > 2;

This query selects all orders that contain more than two items.

order_id |              items
----------+---------------------------------
        1 | {Shirt,Pants,Shoes}
        3 | {Laptop,Mouse,Keyboard,Monitor}
(2 rows)

Use with multi-dimensional arrays

array_length() can be used with multi-dimensional arrays by specifying the dimension to measure.

WITH matrix AS (
  SELECT ARRAY[[1, 2, 3], [4, 5, 6]] AS data
)
SELECT
  array_length(data, 1) AS rows,
  array_length(data, 2) AS columns,
  array_length(data, 3) AS depth
FROM matrix;

This query returns the number of rows and columns in a 2D array. There is no third dimension in this case, so array_length(data, 3) returns NULL.

rows | columns | depth
------+---------+-------
    2 |       3 |
(1 row)

Use in a CHECK constraint

You can use array_length() in a CHECK constraint to enforce a condition based on the size of an array column. For example, consider a table that stores the starting lineup of basketball teams as an array.

CREATE TABLE basketball_team (
  team_name TEXT PRIMARY KEY,
  starting_lineup TEXT[],
  CONSTRAINT check_starting_lineup CHECK (array_length(starting_lineup, 1) = 5)
);

This constraint ensures that the starting_lineup array column always contains exactly five elements.

INSERT INTO basketball_team (team_name, starting_lineup)
VALUES ('Lakers', ARRAY['LeBron James', 'Anthony Davis', 'Russell Westbrook', 'Carmelo Anthony', 'Dwight Howard']);
-- Success

INSERT INTO basketball_team (team_name, starting_lineup)
VALUES ('Warriors', ARRAY['Stephen Curry', 'Klay Thompson', 'Draymond Green']);
-- ERROR:  new row for relation "basketball_team" violates check constraint "check_starting_lineup"
-- DETAIL:  Failing row contains (Warriors, {"Stephen Curry","Klay Thompson","Draymond Green"}).

Additional considerations

Null handling

array_length() returns NULL if the input array is NULL or if the specified dimension does not exist. Always handle potential NULL values in your queries to avoid unexpected results.

Indexing

Note that Postgres array dimensions are indexed starting from 1, not 0. If you specify a dimension less than 1, array_length() returns NULL.

SELECT array_length(ARRAY[1, 2, 3], 0);

Performance implications

array_length() is generally efficient, but be cautious when using it in WHERE clauses on large tables. Consider creating a function index on the array length if you frequently filter based on this condition.

Alternative functions

• cardinality() - Returns the total number of elements in an array, or NULL if the array is NULL. It's equivalent to array_length(anyarray, 1) for one-dimensional arrays.
• array_dims() - Returns a text representation of the array's dimensions.
• array_upper() and array_lower() - Return the upper and lower bounds of the specified array dimension.

Resources

• PostgreSQL documentation: Array Functions and Operators
• PostgreSQL documentation: Arrays