Amazon Redshift will no longer support the use of Python UDFs after June 30, 2026.
We will start enforcing it in phases. For more information on the details of Python end of life
and migration options, see the
blog post
ARRAY_EXCEPT function
Returns the difference between two arrays by keeping elements from the first array that do not exist in the second array. The function is NULL-safe, meaning it treats NULLs are treated as known objects.
Syntax
ARRAY_EXCEPT( array1, array2 [, distinct] )
Arguments
- array1
-
A SUPER expression that specifies the first array.
- array2
-
A SUPER expression that specifies the second array.
- distinct
-
A boolean value that specifies whether to return distinct elements only:
distinct = FALSE: Multi-set semantics apply. Each occurrence of an element in the first array is matched against occurrences in the second array. If the first array has more occurrences of an element than the second array, the extra occurrences are preserved in the result.
distinct = TRUE: Set semantics apply. Both arrays are treated as sets, ignoring duplicate elements. Elements from the first array are removed if they exist anywhere in the second array, regardless of occurrence count.
The default is FALSE.
Return type
The ARRAY_EXCEPT function returns a SUPER type.
Example
The following examples show the ARRAY_EXCEPT function.
SELECT ARRAY_EXCEPT(ARRAY('a','b','c'), ARRAY('b','c','d')); array_except -------------- ["a"] (1 row)
Multi-set semantics:
SELECT ARRAY_EXCEPT(ARRAY('b','b','b','b'), ARRAY('b','b')); array_except -------------- ["b","b"] (1 row)
Set semantics:
SELECT ARRAY_EXCEPT(ARRAY('a','b','b'), ARRAY('b'), TRUE); array_except -------------- ["a"] (1 row)
NULLs are treated as known object.
SELECT ARRAY_EXCEPT(ARRAY('a',NULL), ARRAY(NULL)); array_except -------------- ["a"] (1 row)
See also
