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 and y, without duplicates.

array_union(x, y) → array

Returns an array of the elements in the union of x and y, without duplicates.

array_except(x, y) → array

Returns an array of elements in x but not in y, 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 array x (or 0 if not found).

array_remove(x, element) → array

Remove all elements that equal element from array x.

array_sort(x) → array

Sorts and returns the array x. The elements of x must be orderable. Null elements will be placed at the end of the returned array.

arrays_overlap(x, y) → boolean

Tests if arrays x and y have any any non-null elements in common. Returns null if there are no non-null elements in common but either array contains null.

cardinality(x) → bigint

Returns the cardinality (size) of the array x.

concat(array1, array2, ..., arrayN) → array

Concatenates the arrays array1, array2, ..., arrayN. This function provides the same functionality as the SQL-standard concatenation operator (||).

contains(x, element) → boolean

Returns true if the array x contains the element.

element_at(array<E>, index) → E

Returns element of array at given index. If index >= 0, this function provides the same functionality as the SQL-standard subscript operator ([]). If index < 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 an array(T) by concatenating the contained arrays.

reduce(array, initialState, inputFunction, outputFunction) → x

See reduce().

repeat(element, count) → array

Repeat element for count times.

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 to stop, incrementing by 1 if start is less than or equal to stop, otherwise -1.

sequence(start, stop, step) → array<bigint>

Generate a sequence of integers from start to stop, incrementing by step.

sequence(start, stop, step) → array<timestamp>

Generate a sequence of timestamps from start to stop, incrementing by step. The type of step can be either INTERVAL DAY TO SECOND or INTERVAL 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 index start (or starting from the end if start is negative) with a length of length.

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().