AnalyticDB for PostgreSQL supports the array functions and operators of PostgreSQL. This topic covers all supported operators and functions with examples.
For the full PostgreSQL reference, see Array Functions and Operators.
Array operators
Array operators work on values of the ARRAY type. They compare, test membership, and concatenate arrays.
| Operator | Description | Example | Result |
|---|---|---|---|
= | Returns true if two arrays are equal. | ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] | t |
<> | Returns true if two arrays are not equal. | ARRAY[1,2,3] <> ARRAY[1,2,4] | t |
< | Returns true if the left array is less than the right array. | ARRAY[1,2,3] < ARRAY[1,2,4] | t |
> | Returns true if the left array is greater than the right array. | ARRAY[1,4,3] > ARRAY[1,2,4] | t |
<= | Returns true if the left array is less than or equal to the right array. | ARRAY[1,2,3] <= ARRAY[1,2,3] | t |
>= | Returns true if the left array is greater than or equal to the right array. | ARRAY[1,4,3] >= ARRAY[1,4,3] | t |
@> | Returns true if the left array contains all elements of the right array. | ARRAY[1,4,3] @> ARRAY[3,1,3] | t |
<@ | Returns true if all elements of the left array are contained in the right array. | ARRAY[2,2,7] <@ ARRAY[1,7,4,2,6] | t |
&& | Returns true if the two arrays have at least one element in common. | ARRAY[1,4,3] && ARRAY[2,1] | t |
|| | Concatenates two arrays. | ARRAY[1,2,3] || ARRAY[4,5,6] | {1,2,3,4,5,6} |
|| | Concatenates a one-dimensional array and a multidimensional array. | ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]] | {{1,2,3},{4,5,6},{7,8,9}} |
|| | Prepends an element to an array. | 3 || ARRAY[4,5,6] | {3,4,5,6} |
|| | Appends an element to an array. | ARRAY[4,5,6] || 7 | {4,5,6,7} |
Array functions
Array functions let you construct, inspect, and transform arrays.
| Function | Return type | Description | Example | Result |
|---|---|---|---|---|
array_append(any array, any element) | any array | Appends an element to the end of an array. | array_append(ARRAY[1,2], 3) | {1,2,3} |
array_cat(any array, any array) | any array | Concatenates two arrays. | array_cat(ARRAY[1,2,3], ARRAY[4,5]) | {1,2,3,4,5} |
array_ndims(any array) | int | Returns the number of dimensions of an array. | array_ndims(ARRAY[[1,2,3], [4,5,6]]) | 2 |
array_dims(any array) | text | Returns the text representation of the array dimensions. | array_dims(ARRAY[[1,2,3], [4,5,6]]) | [1:2][1:3] |
array_fill(any element, int[], [, int[]]) | any array | Returns an array of the specified dimensions, filled with the given value. The optional third argument sets a lower bound other than 1. | array_fill(7, ARRAY[3], ARRAY[2]) | [2:4]={7,7,7} |
array_length(any array, int) | int | Returns the length of the requested array dimension. | array_length(array[1,2,3], 1) | 3 |
array_lower(any array, int) | int | Returns the lower bound of the requested array dimension. | array_lower('[0:2]={1,2,3}'::int[], 1) | 0 |
array_position(any array, any element [, int]) | int | Returns the subscript of the first occurrence of the second argument in the array. Search starts at the first element, or at the position given by the third argument. Supported for one-dimensional arrays only. | array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon') | 2 |
array_positions(any array, any element) | int[] | Returns an array of subscripts of all occurrences of the second argument in the array. Supported for one-dimensional arrays only. | array_positions(ARRAY['A','A','B','A'], 'A') | {1,2,4} |
array_prepend(any element, any array) | any array | Prepends an element to the beginning of an array. | array_prepend(1, ARRAY[2,3]) | {1,2,3} |
array_remove(any array, any element) | any array | Removes all elements equal to the given value. Supported for one-dimensional arrays only. | array_remove(ARRAY[1,2,3,2], 2) | {1,3} |
array_replace(any array, any element, any element) | any array | Replaces all elements 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(any array, text [, text]) | text | Concatenates array elements with the specified delimiter. The optional third argument replaces NULL elements in the output. | array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*') | 1,2,3,*,5 |
array_upper(any array, int) | int | Returns the upper bound of the requested array dimension. | array_upper(ARRAY[1,8,3,7], 1) | 4 |
cardinality(any array) | int | Returns the total number of elements in an array across all dimensions. Returns 0 for an empty array. | cardinality(ARRAY[[1,2],[3,4]]) | 4 |
string_to_array(text, text [, text]) | text[] | Splits a string into array elements at each occurrence of the delimiter. The optional third argument specifies a value to treat as NULL in the output. | string_to_array('xx~^~yy~^~zz', '~^~', 'yy') | {xx,NULL,zz} |
unnest(any array) | set of any element | Expands an array into a set of rows, one row per element. | unnest(ARRAY[1,2]) | 1 / 2 |
unnest(any array, any array [, ...]) | set of any element, any element [, ...] | Expands multiple arrays of potentially different types into a set of rows, aligned by position. Shorter arrays are padded with NULLs. Must be used in the FROM clause. | unnest(ARRAY[1,2],ARRAY['foo','bar','baz']) | 1 foo / 2 bar / NULL baz |