15.17. Array Functions and Operators
Subscript Operator: []
The []
operator is used to access an element of an array and is indexed starting from one:
SELECT my_array[1] AS first_element
Concatenation Operator: ||
The ||
operator is used to concatenate an array with an array or an element of the same type:
SELECT ARRAY [1] || ARRAY [2]; -- [1, 2]
SELECT ARRAY [1] || 2; -- [1, 2]
SELECT 2 || ARRAY [1]; -- [2, 1]
Array Functions
-
array_distinct
(x) → array Remove duplicate values from the array
x
.
-
array_intersect
(x, y) → array Returns an array of the elements in the intersection of
x
andy
, without duplicates.
-
array_union
(x, y) → array Returns an array of the elements in the union of
x
andy
, without duplicates.
-
array_join
(x, delimiter, null_replacement) → varchar Concatenates the elements of the given array using the delimiter and an optional string to replace nulls.
-
array_max
(x) → x Returns the maximum value of input array.
-
array_min
(x) → x Returns the minimum value of input array.
-
array_position
(x, element) → bigint Returns the position of the first occurrence of the
element
in arrayx
(or 0 if not found).
-
array_remove
(x, element) → array Remove all elements that equal
element
from arrayx
.
-
array_sort
(x) → array Sorts and returns the array
x
. The elements ofx
must be orderable. Null elements will be placed at the end of the returned array.
-
cardinality
(x) → bigint Returns the cardinality (size) of the array
x
.
-
concat
(x, y) → array Concatenates the arrays
x
andy
. This function provides the same functionality as the SQL-standard concatenation operator (||
).
-
contains
(x, element) → boolean Returns true if the array
x
contains theelement
.
-
element_at
(array<E>, index) → E Returns element of
array
at givenindex
. Ifindex
>= 0, this function provides the same functionality as the SQL-standard subscript operator ([]
). Ifindex
< 0,element_at
accesses elements from the last to the first.
-
filter
(array, function) → array See
filter()
.
-
flatten
(x) → array Flattens an
array(array(T))
to anarray(T)
by concatenating the contained arrays.
-
reduce
(array, initialState, inputFunction, outputFunction) → x See
reduce()
.
-
reverse
(x) → array Returns an array which has the reversed order of array
x
.
-
sequence
(start, stop) → array<bigint> Generate a sequence of integers from
start
tostop
, incrementing by1
ifstart
is less than or equal tostop
, otherwise-1
.
-
sequence
(start, stop, step) → array<bigint> Generate a sequence of integers from
start
tostop
, incrementing bystep
.
-
sequence
(start, stop, step) → array<timestamp> Generate a sequence of timestamps from
start
tostop
, incrementing bystep
. The type ofstep
can be eitherINTERVAL DAY TO SECOND
orINTERVAL YEAR TO MONTH
.
-
shuffle
(x) → array Generate a random permutation of the given array
x
.
-
slice
(x, start, length) → array Subsets array
x
starting from indexstart
(or starting from the end ifstart
is negative) with a length oflength
.
-
transform
(array, function) → array See
transform()
.
-
zip
(array1, array2[, ...]) → array<row> Merges the given arrays, element-wise, into a single array of rows. The M-th element of the N-th argument will be the N-th field of the M-th output element. If the arguments have an uneven length, missing values are filled with
NULL
.SELECT zip(ARRAY[1, 2], ARRAY['1b', null, '3b']); -- [ROW(1, '1b'), ROW(2, null), ROW(null, '3b')]
-
zip_with
(array1, array2, function) → array See
zip_with()
.