9.19. Array Functions and Operators

Function Description Example(s)
`array_append` ( `anycompatiblearray`, `anycompatible` ) → `anycompatiblearray` Appends an element to the end of an array (same as the `anycompatiblearray` `\|\|` `anycompatible` operator). `array_append(ARRAY[1,2], 3)` → `{1,2,3}`
`array_cat` ( `anycompatiblearray`, `anycompatiblearray` ) → `anycompatiblearray` Concatenates two arrays (same as the `anycompatiblearray` `\|\|` `anycompatiblearray` operator). `array_cat(ARRAY[1,2,3], ARRAY[4,5])` → `{1,2,3,4,5}`
`array_dims` ( `anyarray` ) → `text` Returns a text representation of the array's dimensions. `array_dims(ARRAY[[1,2,3], [4,5,6]])` → `[1:2][1:3]`
`array_fill` ( `anyelement`, `integer[]` [, `integer[]` ] ) → `anyarray` Returns an array filled with copies of the given value, having dimensions of the lengths specified by the second argument. The optional third argument supplies lower-bound values for each dimension (which default to all `1`). `array_fill(11, ARRAY[2,3])` → `{{11,11,11},{11,11,11}}` `array_fill(7, ARRAY[3], ARRAY[2])` → `[2:4]={7,7,7}`
`array_length` ( `anyarray`, `integer` ) → `integer` Returns the length of the requested array dimension. (Produces NULL instead of 0 for empty or missing array dimensions.) `array_length(array[1,2,3], 1)` → `3` `array_length(array[]::int[], 1)` → `NULL` `array_length(array['text'], 2)` → `NULL`
`array_lower` ( `anyarray`, `integer` ) → `integer` Returns the lower bound of the requested array dimension. `array_lower('[0:2]={1,2,3}'::integer[], 1)` → `0`
`array_ndims` ( `anyarray` ) → `integer` Returns the number of dimensions of the array. `array_ndims(ARRAY[[1,2,3], [4,5,6]])` → `2`
`array_position` ( `anycompatiblearray`, `anycompatible` [, `integer` ] ) → `integer` Returns the subscript of the first occurrence of the second argument in the array, or `NULL` if it's not present. If the third argument is given, the search begins at that subscript. The array must be one-dimensional. Comparisons are done using `IS NOT DISTINCT FROM` semantics, so it is possible to search for `NULL`. `array_position(ARRAY['sun', 'mon', 'tue', 'wed', 'thu', 'fri', 'sat'], 'mon')` → `2`
`array_positions` ( `anycompatiblearray`, `anycompatible` ) → `integer[]` Returns an array of the subscripts of all occurrences of the second argument in the array given as first argument. The array must be one-dimensional. Comparisons are done using `IS NOT DISTINCT FROM` semantics, so it is possible to search for `NULL`. `NULL` is returned only if the array is `NULL`; if the value is not found in the array, an empty array is returned. `array_positions(ARRAY['A','A','B','A'], 'A')` → `{1,2,4}`
`array_prepend` ( `anycompatible`, `anycompatiblearray` ) → `anycompatiblearray` Prepends an element to the beginning of an array (same as the `anycompatible` `\|\|` `anycompatiblearray` operator). `array_prepend(1, ARRAY[2,3])` → `{1,2,3}`
`array_remove` ( `anycompatiblearray`, `anycompatible` ) → `anycompatiblearray` Removes all elements equal to the given value from the array. The array must be one-dimensional. Comparisons are done using `IS NOT DISTINCT FROM` semantics, so it is possible to remove `NULL`s. `array_remove(ARRAY[1,2,3,2], 2)` → `{1,3}`
`array_replace` ( `anycompatiblearray`, `anycompatible`, `anycompatible` ) → `anycompatiblearray` Replaces each array element equal to the second argument with the third argument. `array_replace(ARRAY[1,2,5,4], 5, 3)` → `{1,2,3,4}`
`array_reverse` ( `anyarray` ) → `anyarray` Reverses the first dimension of the array. `array_reverse(ARRAY[[1,2],[3,4],[5,6]])` → `{{5,6},{3,4},{1,2}}`
`array_sample` ( `array` `anyarray`, `n` `integer` ) → `anyarray` Returns an array of `n` items randomly selected from `array`. `n` may not exceed the length of `array`'s first dimension. If `array` is multi-dimensional, an “item” is a slice having a given first subscript. `array_sample(ARRAY[1,2,3,4,5,6], 3)` → `{2,6,1}` `array_sample(ARRAY[[1,2],[3,4],[5,6]], 2)` → `{{5,6},{1,2}}`
`array_shuffle` ( `anyarray` ) → `anyarray` Randomly shuffles the first dimension of the array. `array_shuffle(ARRAY[[1,2],[3,4],[5,6]])` → `{{5,6},{1,2},{3,4}}`
`array_sort` ( `array` `anyarray` [, `descending` `boolean` [, `nulls_first` `boolean` ]] ) → `anyarray` Sorts the first dimension of the array. The sort order is determined by the default sort ordering of the array's element type; however, if the element type is collatable, the collation to use can be specified by adding a `COLLATE` clause to the `array` argument. If `descending` is true then sort in descending order, otherwise ascending order. If omitted, the default is ascending order. If `nulls_first` is true then nulls appear before non-null values, otherwise nulls appear after non-null values. If omitted, `nulls_first` is taken to have the same value as `descending`. `array_sort(ARRAY[[2,4],[2,1],[6,5]])` → `{{2,1},{2,4},{6,5}}`
`array_to_string` ( `array` `anyarray`, `delimiter` `text` [, `null_string` `text` ] ) → `text` Converts each array element to its text representation, and concatenates those separated by the `delimiter` string. If `null_string` is given and is not `NULL`, then `NULL` array entries are represented by that string; otherwise, they are omitted. See also `string_to_array`. `array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '')` → `1,2,3,,5`
`array_upper` ( `anyarray`, `integer` ) → `integer` Returns the upper bound of the requested array dimension. `array_upper(ARRAY[1,8,3,7], 1)` → `4`
`cardinality` ( `anyarray` ) → `integer` Returns the total number of elements in the array, or 0 if the array is empty. `cardinality(ARRAY[[1,2],[3,4]])` → `4`
`trim_array` ( `array` `anyarray`, `n` `integer` ) → `anyarray` Trims an array by removing the last `n` elements. If the array is multidimensional, only the first dimension is trimmed. `trim_array(ARRAY[1,2,3,4,5,6], 2)` → `{1,2,3,4}`
`unnest` ( `anyarray` ) → `setof anyelement` Expands an array into a set of rows. The array's elements are read out in storage order. `unnest(ARRAY[1,2])` → 1 2 `unnest(ARRAY[['foo','bar'],['baz','quux']])` → foo bar baz quux
`unnest` ( `anyarray`, `anyarray` [, ... ] ) → `setof anyelement, anyelement [, ... ]` Expands multiple arrays (possibly of different data types) into a set of rows. If the arrays are not all the same length then the shorter ones are padded with `NULL`s. This form is only allowed in a query's FROM clause; see Section 7.2.1.4. `select * from unnest(ARRAY[1,2], ARRAY['foo','bar','baz']) as x(a,b)` → a \| b ---+----- 1 \| foo 2 \| bar \| baz

Function

Description

Example(s)

array_append ( anycompatiblearray, anycompatible ) → anycompatiblearray

Appends an element to the end of an array (same as the anycompatiblearray || anycompatible operator).

array_append(ARRAY[1,2], 3) → {1,2,3}

array_cat ( anycompatiblearray, anycompatiblearray ) → anycompatiblearray

Concatenates two arrays (same as the anycompatiblearray || anycompatiblearray operator).

array_cat(ARRAY[1,2,3], ARRAY[4,5]) → {1,2,3,4,5}

array_dims ( anyarray ) → text

Returns a text representation of the array's dimensions.

array_dims(ARRAY[[1,2,3], [4,5,6]]) → [1:2][1:3]

array_fill ( anyelement, integer[] [, integer[] ] ) → anyarray

Returns an array filled with copies of the given value, having dimensions of the lengths specified by the second argument. The optional third argument supplies lower-bound values for each dimension (which default to all 1).

array_fill(11, ARRAY[2,3]) → {{11,11,11},{11,11,11}}

array_fill(7, ARRAY[3], ARRAY[2]) → [2:4]={7,7,7}

array_length ( anyarray, integer ) → integer

Returns the length of the requested array dimension. (Produces NULL instead of 0 for empty or missing array dimensions.)

array_length(array[1,2,3], 1) → 3

array_length(array[]::int[], 1) → NULL

array_length(array['text'], 2) → NULL

array_lower ( anyarray, integer ) → integer

Returns the lower bound of the requested array dimension.

array_lower('[0:2]={1,2,3}'::integer[], 1) → 0

array_ndims ( anyarray ) → integer

Returns the number of dimensions of the array.

array_ndims(ARRAY[[1,2,3], [4,5,6]]) → 2

array_position ( anycompatiblearray, anycompatible [, integer ] ) → integer

Returns the subscript of the first occurrence of the second argument in the array, or NULL if it's not present. If the third argument is given, the search begins at that subscript. The array must be one-dimensional. Comparisons are done using IS NOT DISTINCT FROM semantics, so it is possible to search for NULL.

array_position(ARRAY['sun', 'mon', 'tue', 'wed', 'thu', 'fri', 'sat'], 'mon') → 2

array_positions ( anycompatiblearray, anycompatible ) → integer[]

Returns an array of the subscripts of all occurrences of the second argument in the array given as first argument. The array must be one-dimensional. Comparisons are done using IS NOT DISTINCT FROM semantics, so it is possible to search for NULL. NULL is returned only if the array is NULL; if the value is not found in the array, an empty array is returned.

array_positions(ARRAY['A','A','B','A'], 'A') → {1,2,4}

array_prepend ( anycompatible, anycompatiblearray ) → anycompatiblearray

Prepends an element to the beginning of an array (same as the anycompatible || anycompatiblearray operator).

array_prepend(1, ARRAY[2,3]) → {1,2,3}

array_remove ( anycompatiblearray, anycompatible ) → anycompatiblearray

Removes all elements equal to the given value from the array. The array must be one-dimensional. Comparisons are done using IS NOT DISTINCT FROM semantics, so it is possible to remove NULLs.

array_remove(ARRAY[1,2,3,2], 2) → {1,3}

array_replace ( anycompatiblearray, anycompatible, anycompatible ) → anycompatiblearray

Replaces each array element equal to the second argument with the third argument.

array_replace(ARRAY[1,2,5,4], 5, 3) → {1,2,3,4}

array_reverse ( anyarray ) → anyarray

Reverses the first dimension of the array.

array_reverse(ARRAY[[1,2],[3,4],[5,6]]) → {{5,6},{3,4},{1,2}}

array_sample ( array anyarray, n integer ) → anyarray

Returns an array of n items randomly selected from array. n may not exceed the length of array's first dimension. If array is multi-dimensional, an “item” is a slice having a given first subscript.

array_sample(ARRAY[1,2,3,4,5,6], 3) → {2,6,1}

array_sample(ARRAY[[1,2],[3,4],[5,6]], 2) → {{5,6},{1,2}}

array_shuffle ( anyarray ) → anyarray

Randomly shuffles the first dimension of the array.

array_shuffle(ARRAY[[1,2],[3,4],[5,6]]) → {{5,6},{1,2},{3,4}}

array_sort ( array anyarray [, descending boolean [, nulls_first boolean ]] ) → anyarray

Sorts the first dimension of the array. The sort order is determined by the default sort ordering of the array's element type; however, if the element type is collatable, the collation to use can be specified by adding a COLLATE clause to the array argument.

If descending is true then sort in descending order, otherwise ascending order. If omitted, the default is ascending order. If nulls_first is true then nulls appear before non-null values, otherwise nulls appear after non-null values. If omitted, nulls_first is taken to have the same value as descending.

array_sort(ARRAY[[2,4],[2,1],[6,5]]) → {{2,1},{2,4},{6,5}}

array_to_string ( array anyarray, delimiter text [, null_string text ] ) → text

Converts each array element to its text representation, and concatenates those separated by the delimiter string. If null_string is given and is not NULL, then NULL array entries are represented by that string; otherwise, they are omitted. See also string_to_array.

array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*') → 1,2,3,*,5

array_upper ( anyarray, integer ) → integer

Returns the upper bound of the requested array dimension.

array_upper(ARRAY[1,8,3,7], 1) → 4

cardinality ( anyarray ) → integer

Returns the total number of elements in the array, or 0 if the array is empty.

cardinality(ARRAY[[1,2],[3,4]]) → 4

trim_array ( array anyarray, n integer ) → anyarray

Trims an array by removing the last n elements. If the array is multidimensional, only the first dimension is trimmed.

trim_array(ARRAY[1,2,3,4,5,6], 2) → {1,2,3,4}

unnest ( anyarray ) → setof anyelement

Expands an array into a set of rows. The array's elements are read out in storage order.

unnest(ARRAY[1,2]) →

 1
 2

unnest(ARRAY[['foo','bar'],['baz','quux']]) →

 foo
 bar
 baz
 quux

unnest ( anyarray, anyarray [, ... ] ) → setof anyelement, anyelement [, ... ]

Expands multiple arrays (possibly of different data types) into a set of rows. If the arrays are not all the same length then the shorter ones are padded with NULLs. This form is only allowed in a query's FROM clause; see Section 7.2.1.4.

select * from unnest(ARRAY[1,2], ARRAY['foo','bar','baz']) as x(a,b) →

 a |  b
---+-----
 1 | foo
 2 | bar
   | baz