ARRAY_UPPER

Return the upper bound (largest subscript) of an array dimension.

Category: collectionReturns: INTDialect: PostgreSql

Syntax

ARRAY_UPPER(array, dim)

Description

## Overview Returns the largest subscript (upper bound) of the specified dimension of an array. Combined with ARRAY_LOWER, it produces the closed subscript range along that dimension. For this engine's 1-based arrays, ARRAY_UPPER of dimension 1 is equivalent to ARRAY_SIZE. Typical uses include composing an explicit index range for iteration, validating the shape of a multi-dimensional array, and writing portable code that must address both endpoints of a subscript range. ## Behavior - Returns an INT equal to the largest subscript of the requested dimension. - Dimensions are 1-based; this engine uses 1-based array indexing, so ARRAY_UPPER(array, 1) equals ARRAY_SIZE(array). - Returns NULL for NULL inputs, empty arrays, and non-existent dimensions. - Inspects metadata only; it does not copy or scan array contents. - Pairs naturally with ARRAY_LOWER and GENERATE_SUBSCRIPTS for safe iteration. ## Null and empty handling - NULL input array returns NULL. - Empty array returns NULL (no subscript range is defined). - Non-existent dimension returns NULL rather than raising. - NULL elements inside the array do not affect the bound. ## Compatibility - Matches the standard-compatible ARRAY_UPPER convention. With 1-based indexing ARRAY_UPPER(arr, 1) equals ARRAY_SIZE(arr). - Use GENERATE_SUBSCRIPTS(arr, dim) to materialise the subscript range rather than computing LOWER and UPPER separately.

Parameters

Name	Type	Description
`array`		Specifies the input array whose upper bound is returned.
`dim`		Specifies the 1-based dimension for which to return the upper bound.

Examples

-- Upper bound of a 3-element array
SELECT ARRAY_UPPER(ARRAY[10, 20, 30], 1);  -- 3

-- Upper bound of the second dimension of a 2D array
SELECT ARRAY_UPPER(ARRAY[[1, 2, 3], [4, 5, 6]], 2);  -- 3

-- NULL array returns NULL
SELECT ARRAY_UPPER(CAST(NULL AS ARRAY<INT>), 1);  -- NULL

-- Empty array has no upper bound
SELECT ARRAY_UPPER(CAST(ARRAY() AS ARRAY<INT>), 1);  -- NULL

-- Derive the last index of a per-session trail
SELECT session_id, ARRAY_UPPER(events, 1) AS last_index
FROM analytics.events.user_sessions;

-- Compose with ARRAY_LOWER to produce a full index range
SELECT GENERATE_SUBSCRIPTS(ARRAY['a', 'b', 'c'], 1) AS idx;

Pitfalls

For 1-based arrays ARRAY_UPPER equals ARRAY_SIZE. Prefer ARRAY_SIZE or CARDINALITY for clarity when you only need the element count.
Empty arrays return NULL for every dimension. Add an explicit NULL check when iterating subscript ranges.
A dimension beyond the array's rank returns NULL, not an error. Use ARRAY_NDIMS to validate ranks up front.
Multi-dimensional support assumes rectangular shape. Ragged ARRAY<ARRAY<T>> values only report the outermost upper bound; measure inner arrays per element.