Array functions and operators
Array functions
array_append
Appends any_compatible to the end of the input array.
array_append ( array, any_compatible ) → array
array_append(array[66], 123) → {66, 123}
array_cat
Concatenates two arrays with the same data type.
If the one of the input arrays is a 2-dimensional array, the other array will be appended within the first array as the last element if it is the second argument. The other array will be prepended within the first array as the first element if it is the first argument.
array_cat ( array, array ) → array
array_cat(array[66], array[123]) → {66, 123}
array_cat(array[array[66]], array[233]) → {{66}, {233}}
array_cat(array[233], array[array[66]]) → {{233}, {66}}
array_dims
Returns the dimensions of an array as a string. The array must be one dimensional.
array_dims ( array ) → string
array_dims(array[2,3,4]) → [1:3]
array_distinct
Returns an array of the same type as the input array with all duplicate values removed.
array_distinct ( array ) → array
array_distinct(array[1,2,1,1]) → {1,2}
array_length
This function has two variants.
array_length ( array )
Returns the length of array.
array_length ( array ) → int
array_length(array[1,2,3,4,1]) → 5
array_length ( array int )
Returns the length of the requested array dimension in array. int must be 1.
array_length ( array, int ) → int
array_length(array[2, 3, 4], 1) → 3
array_lower
Returns the lower bound of the requested array dimension in array. (This is always 1 or null.)
array_lower ( array, int ) → int
array_lower(array[2, 3, 4], 1) → 1
array_max
Returns the maximum value in an array.
Null elements are skipped, but if the array contains only null elements, NULL is returned.
array_max ( array ) → type of the elements
array_max(array[3.14, 1.14, 1.14514]) → 3.14
array_max(array[date'2002-10-30', date'2023-09-06', date'2017-06-18']) → 2023-09-06
array_max(array['','']) → empty
array_max(array['a', 'b', NULL, 'c']) → c
array_max(array[NULL]) → NULL
array_min
Returns the minimum value in an array.
Null elements are skipped, but if the array contains only null elements, NULL is returned.
array_min ( array ) → type of the elements
array_min(array['a', 'b', 'c']) → a
array_min(array[date'2002-10-30', date'2023-09-06', date'2017-06-18']) → 2002-10-30
array_min(array['','']) → empty
array_min(array['a', 'b', NULL, 'c']) → a
array_min(array[NULL]) → NULL
array_ndims
Returns the number of dimensions of array.
array_ndims ( array ) → int
array_ndims(array[array[2, 3], array[4, 5]]) → 2
array_position
Returns the subscript of the first occurrence of any_compatible element in array.
array_position ( array, any_compatible ) → int
array_position(array[1,2,3,4,5,6,1,2,3,4,5,6], 4) → 4
array_positions
Returns an array of the subscripts of all occurrences of any_compatible element in array.
array_positions ( array, any_compatible ) → array
array_positions(array[1,2,3,4,5,6,1,2,3,4,5,6], 4) → {4, 10}
array_prepend
Prepends any_compatible to the beginning of the input array.
array_prepend ( any_compatible, array ) → array
array_prepend(123, array[66]) → {123, 66}
array_remove
Returns an array with all occurrences of any_compatible element removed. Multidimensional arrays are also supported.
array_remove ( array, any_compatible ) → array
array_remove(array[array[1],array[2],array[3],array[2]], array[2]) → {{1},{3}}
array_replace
Returns an array with all occurrences of current_element replaced with new_element. Multidimensional arrays are also supported. When the array is multidimensional, the element must be an array of one less dimension. Recursively replacing the base element of a multidimensional array is not supported.
array_replace ( array, current_element, new_element ) → array
array_replace(array[7, null, 8, null], null, 0.5) → {7,0.5,8,0.5}
array_sort
Sorts the elements of an array in ascending order.
array_sort ( array ) → array
array_sort(array[-1000, 2000, 0]) → {-1000,0,2000}
array_sort(array['abcdef', 'aacedf', 'aaadef']) → {aaadef,aacedf,abcdef}
array_sort(array[3, 2, NULL, 1, NULL]) → {1,2,3,NULL,NULL}
array_sum
Returns the sum of the values in the array. Null elements are skipped.
array_sum ( array ) → type of the elements
array_sum(array[3, 2, NULL]) → 5
array_sum(array[-10, 20, -30]) → -20
array_sum(array[interval'4 hour', interval'3 day']) → '3 days 04:00:00'
array_to_string and array_join
Converts an array to a string. The optional delimiter_string separates the array's elements in the resulting string, and the optional null_string represents NULL elements in the array. array_join can also be used instead of array_to_string.
array_to_string ( array, delimiter_string, null_string ) → string
array_join(array, delimiter_string, null_string) → string
array_to_string (array[1, 2, 3, NULL, 5], ',', '*') → 1,2,3,*,5
array_join(array[1, 2, 3, NULL, 5], ',', '*') → 1,2,3,*,5
array_transform
This function takes an array, transforms the elements, and returns the results in a new array. The output array always has the same length as the input array.
array_transform ( array_expression, lambda_expression )
lambda_expression:
| element_alias | transform_expression
Each element in array_expression is evaluated against the transform_expression. element_alias is an alias that represents an array element.
SELECT array_transform('{1,2,3}'::int[], |x| (x::double precision+0.5));
------RESULT
{1.5,2.5,3.5}
SELECT array_transform(
ARRAY['Apple', 'Airbnb', 'Amazon', 'Facebook', 'Google', 'Microsoft', 'Netflix', 'Uber'],
|x| case when x ilike 'A%' then 'A' else 'Other' end
);
------RESULT
{A,A,A,Other,Other,Other,Other,Other}
Note that the transform_expression does not support referencing columns. For example, if you have a table:
CREATE TABLE t(v int, arr int[]);
The following query will fail.
select array_transform(arr, |x| x + v) from t;
array_upper
Returns the upper bound of the requested array dimension in array. int must be 1. (This will return the same value as array_length.)
array_upper ( array, int ) → int
array_upper(array[array[2, 3, 4], array[3, 4, 5]], 1) → 2
cardinality
Returns the total number of elements in array or 0 if the array is empty.
cardinality ( array ) → int
cardinality(array[array[array[3,4,5],array[2,2,2]],array[array[6,7,8],array[0,0,0]]]) → 12
string_to_array
Converts a string to an array. The optional delimiter_string separates the string's elements to create the resulting array, and the optional null_string represents NULL elements in the array.
string_to_array ( string, delimiter_string, null_string ) → array
string_to_array('a b c', ' ', 'a') → {NULL,b,c}
trim_array
Trims an array by removing the last n elements. If the array is multidimensional, only the first dimension is trimmed.
trim_array ( array, num_of_elements_to_trim ) → array
trim_array(array[1,2,3,4,5,NULL], 4) → {1,2}
unnest
Expands an array, or combination of arrays, into a set of rows. The array's elements are output in the order they are stored.
unnest ( array ) → set_of_any_element
unnest(Array[Array[1,3,4,5],Array[2,3]]) →
1
3
4
5
2
3
Array operators
array @> array -> boolean
This operator checks if the left array contains all elements of the right array.
array[1,2,3] @> array[2,3] → t
array <@ array -> boolean
This operator checks if the left array is contained by the right array.
array[2,3] <@ array[1,2,3] → t
array || anycompatible → array
Appends any_compatible to the end of array. This operation achieves the same result as using array_append.
array[66] || 123 → {66, 123}
array || array → array
Concatenates two arrays with the same data type. This operation achieves the same result as using array_cat.
array[66] || array[123] → {66, 123}
anycompatible || array → array
Prepends any_compatible to the beginning of array. This operation achieves the same result as using array_prepend.
123 || array[66] → {123, 66}