ARRAY_LOWER

Return the lower bound (starting subscript) of an array dimension.

Category: collectionReturns: INTDialect: PostgreSql

Syntax

ARRAY_LOWER(array, dim)

Description

## Overview Returns the starting subscript (lower bound) of a given dimension of an array. ARRAY_LOWER complements ARRAY_UPPER to produce the closed subscript range along a dimension, and it is primarily used in ports of code written against dialects that allow arbitrary lower bounds. In this engine, array subscripts are always 1-based, so ARRAY_LOWER returns 1 for every populated dimension of a populated array. The function remains useful for writing portable index-range expressions and for disambiguating NULL and empty arrays. ## Behavior - Returns an INT equal to the starting subscript of the requested dimension. - Dimensions are 1-based. - Returns 1 for every existing dimension of a non-empty array because this engine does not support custom lower bounds. - Does not copy or materialise the array; it inspects metadata only. - Pairs naturally with ARRAY_UPPER and GENERATE_SUBSCRIPTS for 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 bounds. ## Compatibility - Matches the standard-compatible ARRAY_LOWER shape. Engines that always use 1-based indexing (including this one) effectively return 1 for every valid call. - Use GENERATE_SUBSCRIPTS(array, dim) to enumerate the full subscript range when combined with ARRAY_UPPER.

Parameters

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

Examples

-- Lower bound of a standard array (always 1 for 1-based arrays)
SELECT ARRAY_LOWER(ARRAY[10, 20, 30], 1);  -- 1

-- Lower bound of a 2D array, first dimension
SELECT ARRAY_LOWER(ARRAY[[1, 2], [3, 4]], 1);  -- 1

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

-- Empty array has no bounds
SELECT ARRAY_LOWER(CAST(ARRAY() AS ARRAY<INT>), 1);  -- NULL

-- Validate shape before iterating per-session arrays
SELECT session_id
FROM analytics.events.user_sessions
WHERE ARRAY_LOWER(events, 1) IS NOT NULL;

-- Compose with ARRAY_UPPER to derive an index range
SELECT ARRAY_LOWER(ARRAY[10, 20, 30], 1) AS lo,
       ARRAY_UPPER(ARRAY[10, 20, 30], 1) AS hi;  -- 1, 3

Pitfalls

This engine uses 1-based indexing only; ARRAY_LOWER always returns 1 for populated dimensions. Do not write code that expects 0 or other custom lower bounds.
Empty arrays return NULL for every dimension. Consumers must check for NULL before iterating.
A dimension beyond the array's rank returns NULL rather than raising. Combine with ARRAY_NDIMS if you need to detect invalid dimensions up front.
ARRAY_LOWER inspects metadata only. It does not reflect NULL elements or any element-level content.