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_flatten

Added in v2.5.0.

Flattens an array of arrays into a single array. If the input array contains multiple levels of nested arrays, the function will only flatten the first level.

array_flatten(array[array[1,2], array[3,4]]) → {1,2,3,4}

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.

Example A

SELECT array_transform('{1,2,3}'::int[], |x| (x::double precision+0.5));
------RESULT
{1.5,2.5,3.5}

Example B

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

Best practices

When using the unnest function, consider the following performance-optimized patterns:

For aggregation: If you want to unnest an array and then aggregate the values back to a scalar, consider using functions like array_sum directly against the array. This approach is preferred over using unnest and then sum to aggregate. Pure functions like array_sum have much better performance than relational level aggregation, which introduces additional states.

For unnesting: If you want to unnest the array only, use unnest in the SELECT clause directly instead of in a correlated subquery. Correlated subqueries introduce additional states, which impacts performance. When you need to add ordinality to each unnested row, you can use unnest together with generate_series.

Given this example table:

CREATE TABLE example (v1 int, v2 int[]);
INSERT INTO example VALUES (1, array[5,6,7]);

For aggregation:

✅ Recommended

Use array aggregate functions directly:

SELECT v1, array_sum(v2) AS sum FROM example;

❌ Not recommended

Avoid using unnest in correlated subqueries for aggregation:

SELECT v1, (SELECT sum(item) FROM unnest(example.v2) AS item) AS sum FROM example;

For array elements with ordinality:

✅ Recommended

Use generate_series with unnest:

SELECT v1, unnest(v2) AS item, generate_series(1, array_length(v2), 1) AS ord FROM example;

❌ Not recommended

Avoid using WITH ORDINALITY:

SELECT v1, item, ord FROM example, unnest(v2) WITH ORDINALITY AS foo(item, ord);

Note: If you’re using unnest for row-level processing, followed by an array_agg aggregation, it can incur additional overhead due to subplan execution. Use EXPLAIN to examine your query plan, and see if there are unexpected JOINs in your query. If so, please consider rewriting with array_transform to perform your mapping logic to avoid the subplan execution.

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}