ARRAY_REMOVE()

All functions > ARRAY > ARRAY_REMOVE()

Returns an array with all occurrences of the specified element removed.

Signatures

Returns: A new array with all occurrences of the specified element removed

ARRAY_REMOVE(array: ARRAY<T>, element: T) → ARRAY<T>

Notes

• Removes ALL occurrences of the specified element
• Result keeps array position
• Returns empty array if all elements are removed
• NULL elements stay unless the searched value is NULL
• Searching for NULL is a no-op (SQL three-valued membership: NULL does not match NULL)
• Supports ARRAY<ROW> and ARRAY<ARRAY<T>>: element equality uses IS DISTINCT FROM (exact structural match)
• On DOUBLE, an optional third argument is comparison digits (EQUALS at N decimal places), not a start index. Omitting it uses 15 digits and warns

See also

• Array functions

Examples

RETURN
    -- Remove all occurrences of 2
    f1 := ARRAY_REMOVE(ARRAY(1, 2, 2, 3, 2), 2),
    -- Remove all occurrences of 'A'
    f2 := ARRAY_REMOVE(ARRAY('A', 'B', 'A', 'C'), 'A'),
    -- Element not in array
    f3 := ARRAY_REMOVE(ARRAY(1, 2, 3), 4),
    -- Remove all elements
    f4 := ARRAY_REMOVE(ARRAY(5, 5, 5), 5),
    -- Remove specific string
    f5 := ARRAY_REMOVE(ARRAY('apple', 'banana'), 'apple'),
    -- Empty array
    f6 := ARRAY_REMOVE(ARRAY()::ARRAY<BIGINT>, 1),
    -- NULL needle is a no-op
    f7 := ARRAY_REMOVE(ARRAY(1, NULL::BIGINT, 2, NULL::BIGINT), NULL::BIGINT)
;