Insert an element at a given position in an array, shifting subsequent elements right.
ARRAY_INSERT(array, pos, element)## Overview Returns a new array that contains the input array's elements with the supplied element inserted at the given 1-based position. All existing elements at or after that position are shifted one index to the right. ARRAY_INSERT is the immutable counterpart to an in-place splice and is the preferred way to patch a fixed-shape array in a projection. Typical uses include splicing a corrective or synthetic event into a per-row trail, inserting a default value at a reserved slot, and bootstrapping a padded feature vector. ## Behavior - Produces an ARRAY<T> whose length is one greater than the input length. - Indexing is 1-based. Position 1 inserts at the head; position ARRAY_SIZE(array) + 1 inserts at the tail. - Positions outside the range 1 to ARRAY_SIZE(array) + 1 raise a runtime error. To be append-tolerant, compute pos = LEAST(n + 1, ARRAY_SIZE(array) + 1). - Never mutates the input. A fresh array is allocated. - Element type must widen to the array's element type. NULL is allowed. ## Null and empty handling - NULL input array returns NULL. - NULL element inserts a NULL at the requested position without error. - Empty input array only accepts pos = 1 (producing a single-element result); any other position raises an out-of-range error. - The position argument itself is NULL-intolerant: a NULL position raises an error. ## Compatibility - Matches the standard array/map SQL convention with 1-based positions. Some dialects support negative positions counting from the end; this engine uses positive 1-based indexing only. - Equivalent in outcome to CONCAT(SLICE(array, 1, pos - 1), ARRAY(element), SLICE(array, pos, ARRAY_SIZE(array))) but avoids three allocations.
| Name | Type | Description |
|---|---|---|
array | Specifies the input array. The result is a new array with the element inserted at the requested position. | |
pos | Specifies the 1-based position at which to insert the element. Must be in the range 1 to ARRAY_SIZE(array) + 1. Values outside this range raise an error. | |
element | Specifies the element to insert. Must be type-compatible with the array's element type. NULL is accepted and is inserted as a NULL element. |
-- Insert at the head
SELECT ARRAY_INSERT(ARRAY[2, 3, 4], 1, 1); -- [1, 2, 3, 4]-- Insert in the middle
SELECT ARRAY_INSERT(ARRAY[1, 2, 4], 3, 3); -- [1, 2, 3, 4]-- Insert NULL element at position 2
SELECT ARRAY_INSERT(ARRAY[1, 2, 3], 2, CAST(NULL AS INT)); -- [1, NULL, 2, 3]-- Append by inserting at position n+1
SELECT ARRAY_INSERT(ARRAY['a', 'b'], 3, 'c'); -- ['a', 'b', 'c']-- Splice a correction into a per-session event trail
SELECT session_id, ARRAY_INSERT(events, 2, 'correction_applied') AS events
FROM analytics.events.user_sessions;-- NULL input array returns NULL
SELECT ARRAY_INSERT(CAST(NULL AS ARRAY<INT>), 1, 42); -- NULL