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.
Table 9.51. Array Operators
Operator Description Example(s) |
---|
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
|
Is the first array contained by the second?
|
Do the arrays overlap, that is, have any elements in common?
|
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). If the arrays are not of identical element types, they will be coerced to a common type (see Section 10.5).
|
Concatenates an element onto the front of an array (which must be empty or one-dimensional).
|
Concatenates an element onto the end of an array (which must be empty or one-dimensional).
|
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) |
---|
Appends an element to the end of an array (same as
the
|
Concatenates two arrays (same as
the
|
Returns a text representation of the array's dimensions.
|
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
|
Returns the length of the requested array dimension.
|
Returns the lower bound of the requested array dimension.
|
Returns the number of dimensions of the array.
|
Returns the subscript of the first occurrence of the second argument
in the array, or
|
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
|
Prepends an element to the beginning of an array (same as
the
|
Removes all elements equal to the given value from the array.
The array must be one-dimensional.
Comparisons are done using
|
Replaces each array element equal to the second argument with the third argument.
|
Converts each array element to its text representation, and
concatenates those separated by
the
|
Returns the upper bound of the requested array dimension.
|
Returns the total number of elements in the array, or 0 if the array is empty.
|
Trims an array by removing the last
|
Expands an array into a set of rows. The array's elements are read out in storage order.
1 2
foo bar baz quux
|
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
a | b ---+----- 1 | foo 2 | bar | baz
|
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.