PostgreSQL

9.19. Array Functions and Operators
Prev	Up	Chapter 9. Functions and Operators	Home	Next

9.19. Array Functions and Operators

Table 9.51 shows the specialized operators available for array types. In addition to those, the usual comparison operators shown in Table 9.1 are available for arrays. The comparison operators compare the array contents element-by-element, using the default B-tree comparison function for the element data type, and sort based on the first difference. In multidimensional arrays the elements are visited in row-major order (last subscript varies most rapidly). If the contents of two arrays are equal but the dimensionality is different, the first difference in the dimensionality information determines the sort order. (This is a change from versions of PostgreSQL prior to 8.2: older versions would claim that two arrays with the same contents were equal, even if the number of dimensions or subscript ranges were different.)

Table 9.51. Array Operators

Operator Description Example(s)

Operator Description Example(s)
`anyarray` `@>` `anyarray` → `boolean` Does the first array contain the second, that is, does each element appearing in the second array equal some element of the first array? (Duplicates are not treated specially, thus `ARRAY[1]` and `ARRAY[1,1]` are each considered to contain the other.) `ARRAY[1,4,3] @> ARRAY[3,1,3]` → `t`
`anyarray` `<@` `anyarray` → `boolean` Is the first array contained by the second? `ARRAY[2,2,7] <@ ARRAY[1,7,4,2,6]` → `t`
`anyarray` `&&` `anyarray` → `boolean` Do the arrays overlap, that is, have any elements in common? `ARRAY[1,4,3] && ARRAY[2,1]` → `t`
`anyarray` `+

` `+anyarray` → `anyarray` Concatenates the two arrays. Concatenating a null or empty array is a no-op; otherwise the arrays must have the same number of dimensions (as illustrated by the first example) or differ in number of dimensions by one (as illustrated by the second). `+ARRAY[1,2,3]

ARRAY[4,5,6,7]` → `{1,2,3,4,5,6,7}+` `+ARRAY[1,2,3]

ARRAY[[4,5,6],[7,8,9]]` → `{{1,2,3},{4,5,6},{7,8,9}}+`
`anyelement` `+

` `+anyarray` → `anyarray` Concatenates an element onto the front of an array (which must be empty or one-dimensional). `+3

ARRAY[4,5,6]` → `{3,4,5,6}+`
`anyarray` `+

` `+anyelement` → `anyarray` Concatenates an element onto the end of an array (which must be empty or one-dimensional). `+ARRAY[4,5,6]

7+` → `{4,5,6,7}`

anyarray @> anyarray → boolean

Does the first array contain the second, that is, does each element appearing in the second array equal some element of the first array? (Duplicates are not treated specially, thus ARRAY[1] and ARRAY[1,1] are each considered to contain the other.)

ARRAY[1,4,3] @> ARRAY[3,1,3] → t

anyarray <@ anyarray → boolean

Is the first array contained by the second?

ARRAY[2,2,7] <@ ARRAY[1,7,4,2,6] → t

anyarray && anyarray → boolean

Do the arrays overlap, that is, have any elements in common?

ARRAY[1,4,3] && ARRAY[2,1] → t

anyarray `+

` `+anyarray` → anyarray

Concatenates the two arrays. Concatenating a null or empty array is a no-op; otherwise the arrays must have the same number of dimensions (as illustrated by the first example) or differ in number of dimensions by one (as illustrated by the second).

`+ARRAY[1,2,3]

ARRAY[4,5,6,7]` → `{1,2,3,4,5,6,7}+`

`+ARRAY[1,2,3]

ARRAY[[4,5,6],[7,8,9]]` → `{{1,2,3},{4,5,6},{7,8,9}}+`

anyelement `+

` `+anyarray` → anyarray

Concatenates an element onto the front of an array (which must be empty or one-dimensional).

`+3

ARRAY[4,5,6]` → `{3,4,5,6}+`

anyarray `+

` `+anyelement` → anyarray

Concatenates an element onto the end of an array (which must be empty or one-dimensional).

`+ARRAY[4,5,6]

7+` → {4,5,6,7}

See Section 8.15 for more details about array operator behavior. See Section 11.2 for more details about which operators support indexed operations.

Table 9.52 shows the functions available for use with array types. See Section 8.15 for more information and examples of the use of these functions.

Table 9.52. Array Functions

Function Description Example(s)

Function Description Example(s)
`+array_append+` ( `+anyarray+`, `+anyelement+` ) → `+anyarray+` Appends an element to the end of an array (same as the `anyarray` `+

` `+anyelement` operator). `array_append(ARRAY[1,2], 3)` → `{1,2,3}`
`+array_cat+` ( `+anyarray+`, `+anyarray+` ) → `+anyarray+` Concatenates two arrays (same as the `anyarray` `+

` `+anyarray` 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[]+` [[.optional]#, `+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+` ( `+anyarray+`, `+anyelement+` [[.optional]#, `+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+` ( `+anyarray+`, `+anyelement+` ) → `+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+` ( `+anyelement+`, `+anyarray+` ) → `+anyarray+` Prepends an element to the beginning of an array (same as the `anyelement` `+

` `+anyarray` operator). `array_prepend(1, ARRAY[2,3])` → `{1,2,3}`
`+array_remove+` ( `+anyarray+`, `+anyelement+` ) → `+anyarray+` 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+` ( `+anyarray+`, `+anyelement+`, `+anyelement+` ) → `+anyarray+` 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_to_string+` ( _`+array+`_ `+anyarray+`, _`+delimiter+`_ `+text+` [[.optional]#, _`+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. `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`
`+string_to_array+` ( _`+string+`_ `+text+`, _`+delimiter+`_ `+text+` [[.optional]#, _`+null_string+`_ `+text+`# ] ) → `+text[]+` Splits the `string `at occurrences of` delimiter and forms the remaining data into a `text array. If `delimiter is `NULL, each character in the `string `will become a separate element in the array. If` delimiter `is an empty string, then the` string `is treated as a single field. If` null_string is supplied and is not `NULL, fields matching that string are converted to `NULL` entries. `string_to_array('xx~~yy~~zz', '~~', 'yy')` → `{xx,NULL,zz}`
`+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 ----

`+array_append+` ( `+anyarray+`, `+anyelement+` ) → `+anyarray+`

Appends an element to the end of an array (same as the anyarray `+

` `+anyelement` operator).

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

`+array_cat+` ( `+anyarray+`, `+anyarray+` ) → `+anyarray+`

Concatenates two arrays (same as the anyarray `+

` `+anyarray` 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[]+` [[.optional]#, `+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+` ( `+anyarray+`, `+anyelement+` [[.optional]#, `+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+` ( `+anyarray+`, `+anyelement+` ) → `+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+` ( `+anyelement+`, `+anyarray+` ) → `+anyarray+`

Prepends an element to the beginning of an array (same as the anyelement `+

` `+anyarray` operator).

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

`+array_remove+` ( `+anyarray+`, `+anyelement+` ) → `+anyarray+`

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+` ( `+anyarray+`, `+anyelement+`, `+anyelement+` ) → `+anyarray+`

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_to_string+` ( _`+array+`_ `+anyarray+`, _`+delimiter+`_ `+text+` [[.optional]#, _`+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.

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

`+string_to_array+` ( _`+string+`_ `+text+`, _`+delimiter+`_ `+text+` [[.optional]#, _`+null_string+`_ `+text+`# ] ) → `+text[]+`

Splits the `string at occurrences of delimiter and forms the remaining data into a `text array. If `delimiter is `NULL, each character in the `string will become a separate element in the array. If delimiter is an empty string, then the string is treated as a single field. If null_string is supplied and is not `NULL, fields matching that string are converted to NULL entries.

string_to_array('xx~~yy~~zz', '~~', 'yy') → {xx,NULL,zz}

`+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) →

b ---+----- 1

foo 2

bar

baz ----

Note

There are two differences in the behavior of string_to_array from pre-9.1 versions of PostgreSQL. First, it will return an empty (zero-element) array rather than NULL when the input string is of zero length. Second, if the delimiter string is NULL, the function splits the input into individual characters, rather than returning NULL as before.

See also Section 9.21 about the aggregate function array_agg for use with arrays.

Prev	Up	Next
9.18. Conditional Expressions	Home	9.20. Range Functions and Operators

9.18. Conditional Expressions

Home

9.20. Range Functions and Operators

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.