| Function | Description | Example |
|---|
array_append(anyarray, anyelement) → anyarray | Appends an element to the end of an array. Equivalent to the anycompatiblearray || anycompatible operator. | array_append(ARRAY[1,2], 3) → {1,2,3} |
array_cat(anyarray, anyarray) → anyarray | Concatenates two arrays. Equivalent to 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. The second argument specifies the length of each dimension. The optional third argument sets lower-bound values for each dimension (default: 1 for all dimensions). | 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. Returns NULL (not 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 [, integer]) → integer | Returns the subscript of the first occurrence of the second argument in the array, or NULL if not found. The optional third argument sets the starting subscript for the search. The array must be one-dimensional. Uses IS NOT DISTINCT FROM semantics, so NULL values can be searched. | array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon') → 2 |
array_positions(anyarray, anyelement) → integer[] | Returns an array of subscripts for all occurrences of the second argument in the array. The array must be one-dimensional. Uses IS NOT DISTINCT FROM semantics, so NULL values can be searched. Returns NULL only when the input array is NULL; returns an empty array when the value is not found. | 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. Equivalent to the anycompatible || anycompatiblearray 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. Uses IS NOT DISTINCT FROM semantics, so NULL values can be removed. | array_remove(ARRAY[1,2,3,2], 2) → {1,3} |
array_replace(anyarray, anyelement, anyelement) → anyarray | Replaces each 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(anyarray, delimiter text [, null_string text]) → text | Converts each array element to its text representation and joins them with the delimiter string. If null_string is provided and not NULL, 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 |
string_to_array(string text, delimiter text [, null_string text]) → text[] | Splits a string on the delimiter and returns the parts as a text array. If the delimiter is NULL, each character becomes a separate element. If the delimiter is an empty string, the entire string is returned as a single-element array. If null_string is provided and not NULL, fields matching it are converted to NULL. See also array_to_string. | string_to_array('xx~~yy~~zz', '~~', 'yy') → {xx,NULL,zz} |
unnest(anyarray) → SETOF anyelement | Expands an array into a set of rows. Elements are returned in storage order. | unnest(ARRAY[1,2]) → 1 2 |
unnest(anyarray, anyarray [, ...]) → SETOF anyelement, anyelement [, ...] | Expands multiple arrays (possibly of different data types) into a set of rows. Shorter arrays are padded with NULL. This form is only allowed in a query's FROM clause. | SELECT * FROM unnest(ARRAY[1,2], ARRAY['foo','bar','baz']) AS x(a,b) → (1,foo) (2,bar) (NULL,baz) |