ARRAY_DIMS

Return the dimensions of an array as a text representation.

Category: collectionReturns: TEXTDialect: PostgreSql

Syntax

ARRAY_DIMS(array)

Description

## Overview Returns a text representation of the lower and upper bounds of each dimension of an array, formatted as bracket-enclosed bounds of the form '[lower:upper]' per dimension. ARRAY_DIMS is primarily a diagnostic or gating function, used to confirm that an input array has the expected shape before running shape-sensitive logic. Typical uses include validating per-row arrays that must match a known length or rank before a downstream calculation, surfacing metadata about multi-dimensional arrays in reporting, and quickly distinguishing between populated and empty arrays. ## Behavior - Returns a TEXT value describing the bounds of each dimension in order; 1-based lower bounds are used by convention. - Produces one '[lower:upper]' segment per dimension; for one-dimensional arrays the result is a single segment. - Does not allocate or copy the array payload; only metadata is inspected. - The result is always in canonical bracket form; there is no alternative numeric-tuple output. - Does not validate that the array is rectangular in multi-dimensional cases; irregular arrays report the maximum bounds seen. ## Null and empty handling - NULL input array returns NULL. - Empty array returns NULL (no dimensions to report). - Arrays containing NULL elements are still reported by shape; element NULLs do not affect the dimension envelope. ## Compatibility - Matches the standard-compatible ARRAY_DIMS convention. Use ARRAY_LENGTH or ARRAY_SIZE when you only need a numeric length in a single dimension.

Parameters

Name	Type	Description
`array`		Specifies the array whose dimension envelope will be reported. Supports one-dimensional and multi-dimensional arrays.

Examples

-- One-dimensional array with three elements
SELECT ARRAY_DIMS(ARRAY[1, 2, 3]);  -- '[1:3]'

-- Two-dimensional rectangular array
SELECT ARRAY_DIMS(ARRAY[[1, 2], [3, 4]]);  -- '[1:2][1:2]'

-- Per-row dimensions of a measurement array
SELECT session_id, ARRAY_DIMS(measurements) AS dims
FROM analytics.events.user_sessions;

-- Empty array returns NULL
SELECT ARRAY_DIMS(CAST(ARRAY() AS ARRAY<INT>));  -- NULL

-- NULL input returns NULL
SELECT ARRAY_DIMS(CAST(NULL AS ARRAY<INT>));  -- NULL

-- Guard against irregular arrays before expensive per-row work
SELECT session_id
FROM analytics.events.user_sessions
WHERE ARRAY_DIMS(measurements) = '[1:10]';

Pitfalls

ARRAY_DIMS returns TEXT, not a numeric tuple. Parse with string functions if you need to compute arithmetic on the bounds.
Empty arrays return NULL rather than an empty string; downstream comparisons must handle NULL explicitly.
Multi-dimensional arrays must be rectangular for the reported bounds to be accurate. Irregular or ragged nesting is stored as ARRAY<ARRAY<T>> rather than a true matrix.
Array indexes are 1-based in the text representation. Do not assume 0-based bounds when composing dimension-aware logic.