Array functions and operators

Array functions and operators

Note: For an alphabetical listing of the array functions and operators, see the listing in the navigation bar.

Most of the functions and operators listed here can use an array of any dimensionality, but four of the functions accept, or produce, only a one-dimensional array. This property is called out by the second column "1-d only?" in the tables that follow. The restricted status is indicated by "1-d" in that function's row. When the field is blank, there is no dimensionality restriction.

Functions for creating arrays from scratch

The array[] constructor, and the three functions, create an array from scratch.

Function or operator 1-d only? Description
array[] The array[] value constructor is a special variadic function that creates an array value from scratch using an expression for each of the array's values. Such an expression can itself use the array[] constructor or an array literal.
array_fill() Returns a new "blank canvas" array of the specified shape with all cells set to the same specified value.
array_agg() Returns an array (of an implied "row" type) from a SQL subquery.
string_to_array() 1-d Returns a one-dimensional text[] array by splitting the input text value into subvalues using the specified text value as the delimiter. Optionally, allows a specified text value to be interpreted as NULL.

Functions for reporting the geometric properties of an array

Function 1-d only? Description
array_ndims() Returns the dimensionality of the specified array.
array_lower() Returns the lower bound of the specified array along the specified dimension.
array_upper() Returns the upper bound of the specified array along the specified dimension.
array_length() Returns the length of the specified array along the specified dimension.
cardinality() Returns the total number of values in the specified array.
array_dims() Returns a text representation of the same information as array_lower() and array_length(), for all dimensions, in a single text value.

Functions to find a value in an array

Function 1-d only? Description
array_position() 1-d Returns the index, in the supplied array, of the specified value. Optionally starts searching at the specified index.
array_positions() 1-d Returns the indexes, in the supplied array, of all occurrences the specified value.

Operators to test whether a value is in an array

These operators require that the LHS is a scalar and that the RHS is an array of that LHS's data type.

Operator 1-d only? Description
ANY Returns TRUE if at least one of the specified inequality tests between the LHS element and each of the RHS array's elements evaluates to TRUE.
ALL Returns TRUE if every one of the specified inequality tests between the LHS element and each of the RHS array's elements evaluates to TRUE.

Operators for comparing two arrays

These operators require that the LHS and RHS arrays have the same data type.

Operator 1-d only? Description
= Returns TRUE if the LHS and RHS arrays are equal.
<> Returns TRUE if the LHS and RHS arrays are not equal.
> Returns TRUE if the LHS array is greater than the RHS array.
>= Returns TRUE if the LHS array is greater than or equal to the RHS array.
<= Returns TRUE if the LHS array is less than or equal to the RHS array.
< Returns TRUE if the LHS array is less than the RHS array.
@> Returns TRUE if the LHS array contains the RHS array—that is, if every distinct value in the RHS array is found among the LHS array's distinct values.
<@ Returns TRUE if the LHS array is contained by the RHS array—that is, if every distinct value in the LHS array is found among the RHS array's distinct values.
&& Returns TRUE if the LHS and RHS arrays overlap—that is, if they have at least one value in common.

The slice operator

Operator 1-d only? Description
[lb1:ub1]...[lbN:ubN] Returns a new array whose length is defined by specifying the slice's lower and upper bound along each dimension. These specified slicing bounds must not exceed the source array's bounds. The new array has the same dimensionality as the source array and its lower bound is 1 on each axis.

Functions and operators for concatenating an array with an array or an element

These functions require that the two arrays have the same data type and compatible dimensionality.

Function or operator 1-d only? Description
[` `](./concatenation/#the-160-160-160-160-operator)
array_cat() Returns the concatenation of two compatible anyarray values.
array_append() Returns an array that results from appending a scalar value to (that is, after) an array value.
array_prepend() Returns an array that results from prepending a scalar value to (that is, before) an array value.

Functions and operators to change values in an array

Function or operator 1-d only? Description
array_replace() Returns a new array where every occurrence of the specified value in the input array has been replaced by the specified new value.
arr[idx_1]...[idx_N] := val Update a value in an array "in place".
array_remove() 1-d Returns a new array where every occurrence of the specified value has been removed from the specified input array.

Function to convert an array to a text value

Function 1-d only? Description
array_to_string() Returns a text value computed by representing each array value, traversing these in row-major order, by its ::text typecast, using the supplied delimiter between each such representation. (The result, therefore, loses all information about the arrays geometric properties.) Optionally, represent NULL by the supplied text value.

Table function to transform an array into a SETOF anyelement

Function 1-d only? Description
unnest() Use in the FROM clause of a SELECT statement. The simple overload accepts a single anyarray value and returns a SETOF anyelement. The exotic overload accepts a variadic list of anyarray values and returns a SETOF with many columns where each, in turn, has the output of the corresponding simple overload.

Table function to transform an array into a SETOF index values

Function 1-d only? Description
generate_subscripts() Use in the FROM clause of a SELECT statement. Returns the values of the indexes along the specified dimension of the specified array.