PostgreSQL array_length Function

The array_length() function in PostgreSQL returns the length of an array along a specified dimension. It's essential for validating array sizes, filtering by array length, and working with multidimensional arrays.

Syntax

-- Get length of array dimension
array_length(array, dimension)
-- dimension: 1 for first dimension, 2 for second, etc.

-- Alternative: cardinality (total element count)
cardinality(array)

Official Documentation

Example usage

-- Get length of simple array
SELECT array_length(ARRAY[1, 2, 3, 4, 5], 1) AS length;
-- Result: 5

-- Length of text array
SELECT array_length(ARRAY['a', 'b', 'c'], 1) AS length;
-- Result: 3

-- Get array length from table column
SELECT
    product_name,
    array_length(tags, 1) AS tag_count
FROM products;

-- Filter by array length
SELECT * FROM products
WHERE array_length(tags, 1) > 3;

-- Find products with no tags
SELECT * FROM products
WHERE array_length(tags, 1) IS NULL
   OR array_length(tags, 1) = 0;

-- Multidimensional array lengths
SELECT
    array_length(ARRAY[[1,2,3], [4,5,6]], 1) AS dim1_length,  -- 2 (rows)
    array_length(ARRAY[[1,2,3], [4,5,6]], 2) AS dim2_length;  -- 3 (columns)

-- Using cardinality for total count
SELECT cardinality(ARRAY[1, 2, 3, 4]) AS total_elements;
-- Result: 4

-- Cardinality with multidimensional arrays
SELECT cardinality(ARRAY[[1,2], [3,4], [5,6]]) AS total_elements;
-- Result: 6 (all elements regardless of dimension)

-- Compare array lengths
SELECT
    product_name,
    array_length(tags, 1) AS tag_count,
    array_length(images, 1) AS image_count
FROM products
WHERE array_length(tags, 1) > array_length(images, 1);

-- Array length in calculations
SELECT
    user_id,
    scores,
    array_length(scores, 1) AS test_count,
    (SELECT SUM(s) FROM unnest(scores) s) / array_length(scores, 1) AS average_score
FROM student_scores;

-- Handle NULL arrays
SELECT
    COALESCE(array_length(tags, 1), 0) AS tag_count
FROM products;

-- Group by array length
SELECT
    array_length(tags, 1) AS tag_count,
    COUNT(*) AS product_count
FROM products
GROUP BY array_length(tags, 1)
ORDER BY tag_count;

-- Find empty arrays (length 0 vs NULL)
SELECT * FROM products
WHERE tags IS NOT NULL
  AND array_length(tags, 1) = 0;

-- Validate array constraints
SELECT * FROM products
WHERE array_length(tags, 1) BETWEEN 1 AND 10;

-- Calculate array statistics
SELECT
    AVG(array_length(tags, 1)) AS avg_tags,
    MIN(array_length(tags, 1)) AS min_tags,
    MAX(array_length(tags, 1)) AS max_tags
FROM products
WHERE array_length(tags, 1) IS NOT NULL;

-- Dynamic array slicing based on length
SELECT
    tags,
    tags[1:array_length(tags, 1)/2] AS first_half
FROM products
WHERE array_length(tags, 1) > 0;

Common issues

1. NULL arrays: array_length returns NULL for NULL arrays, not 0.
2. Empty arrays: '{}'::TEXT[] has length NULL in PostgreSQL (confusingly).
3. Dimension parameter: Must specify dimension (usually 1 for simple arrays).
4. Zero-indexed vs one-indexed: Array indices are 1-based, but length is the count.

Best practices

1. Use dimension 1 for simple (one-dimensional) arrays.
2. Use cardinality() for total element count regardless of dimensions.
3. Use COALESCE to convert NULL to 0 when needed: COALESCE(array_length(arr, 1), 0).
4. For existence checks, use array_length(arr, 1) > 0 or cardinality(arr) > 0.

Frequently Asked Questions

Q: What's the difference between array_length and cardinality?
A: array_length returns the size along a specific dimension: array_length(arr, 1). cardinality returns the total number of elements across all dimensions. For 1D arrays they're the same, but for 2D arrays, cardinality gives the total count.

Q: Why does array_length return NULL for my array?
A: Either the array itself is NULL, or it's an empty array. Empty arrays '{}' return NULL from array_length. Use COALESCE(array_length(arr, 1), 0) to get 0 instead of NULL.

Q: What dimension should I use for simple arrays?
A: Use dimension 1 for regular one-dimensional arrays: array_length(tags, 1). Dimension 2 and higher are for multidimensional arrays.

Q: How do I check if an array is empty?
A: Check if array_length(arr, 1) IS NULL OR array_length(arr, 1) = 0, or use cardinality(arr) = 0. Better yet: cardinality(arr) IS NULL OR cardinality(arr) = 0.

Q: Can I get the length of nested arrays?
A: array_length only works on the dimensions of the main array structure. For counting elements in nested JSONB arrays, use jsonb_array_length instead.