Skip to main content

Categories: Semi-Structured Data

ARRAY_INSERT

Returns an array that contains all of the elements from the input array as well as a new element inserted in the specified position.

Syntax​

ARRAY_INSERT(arr LIST, position INT, new_element ANY) → LIST​

  • arr: The array to search.
  • position: The zero-based position in the input array where the new element should be inserted.
  • new_element: The new element to insert in the specified position.

Examples

ARRAY_INSERT example 
SELECT ARRAY_INSERT(ARRAY[1, 2, 3, 4, 5], 2, 55);
-- [1, 2, 55, 3, 4, 5]
ARRAY_INSERT example 
SELECT ARRAY_INSERT(ARRAY[1, 2, 3], 6, 55);
-- [1, 2, 3, NULL, NULL, NULL, 55]
ARRAY_INSERT example 
SELECT ARRAY_INSERT(ARRAY[1, 2, 3], -1, 55);
-- [1, 2, 55, 3]

Usage Notes​

• The existing element in the specified position and all subsequent elements are shifted to the right by one position in the output array.

• If the absolute value of the specified position exceeds the number of elements in the input array, then NULL elements are inserted between the last element in the input array and the new element.

• A negative position is interpreted as an index from the end of the array. For example, the position -1 means that the new element should be added as the last element in the array.