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>
sql
ParameterTypeRequiredDescription
arrayARRAY<T>YesThe input array from which to remove elements
elementTYesThe element to remove from the array

Notes

  • Removes ALL occurrences of the specified element
  • Result element order is normalized (often sorted) so behavior is stable across backends
  • Returns empty array if all elements are removed
  • Can be used to remove NULL values from arrays
  • Supports ARRAY<ROW> and ARRAY<ARRAY<T>>: element equality uses IS DISTINCT FROM (exact structural match)

See also

Examples

FeatureQL
SELECT
    -- 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()::BIGINT[], 1),
    -- Remove NULL values
    f7 := ARRAY_REMOVE(ARRAY(1, NULL::BIGINT, 2, NULL::BIGINT), NULL::BIGINT)
;
Result
f1 ARRAYf2 ARRAYf3 ARRAYf4 ARRAYf5 ARRAYf6 ARRAYf7 ARRAY
[1, 3][B, C][1, 2, 3][][banana][][1, 2]

Suggest changes to this page
Last update at: 2026/06/20 10:08:10