PolarDB:Array functions and operators

Array Operators

The following table shows the functions that can be used on array types.

Array functions

1. 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.)

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

2. Is the first array contained by the second?

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

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

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

4. 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.

anyarray || anyarray → anyarray
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}}

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

anyelement || anyarray → anyarray
3 || ARRAY[4,5,6] → {3,4,5,6}

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

anyarray || anyelement → anyarray
ARRAY[4,5,6] || 7 → {4,5,6,7}

Functions

1. Appends an element to the end of an array (same as the anycompatiblearray || anycompatible operator).

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

2. Concatenates two arrays (same as the anycompatiblearray || anycompatiblearray operator).

array_cat ( anyarray, anyarray ) → anyarray
array_cat(ARRAY[1,2,3], ARRAY[4,5]) → {1,2,3,4,5}

3. Returns a text representation of the array's dimensions.

array_dims ( anyarray ) → text
array_dims(ARRAY[[1,2,3], [4,5,6]]) → [1:2][1:3]

4. 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 ( anyelement, integer[] [, integer[] ] ) → anyarray
array_fill(11, ARRAY[2,3]) → {{11,11,11},{11,11,11}}
array_fill(7, ARRAY[3], ARRAY[2]) → [2:4]={7,7,7}

5. Returns the length of the requested array dimension. (Produces NULL instead of 0 for empty or missing array dimensions.)

array_length ( anyarray, integer ) → integer
array_length(array[1,2,3], 1) → 3

6. Returns the lower bound of the requested array dimension.

array_lower ( anyarray, integer ) → integer
array_lower('[0:2]={1,2,3}'::integer[], 1) → 0

7. Returns the number of dimensions of the array.

array_ndims ( anyarray ) → integer
array_ndims(ARRAY[[1,2,3], [4,5,6]]) → 2

8. 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 ( anyarray, anyelement [, integer ] ) → integer
array_position(ARRAY['sun', 'mon', 'tue', 'wed', 'thu', 'fri', 'sat'], 'mon') → 2

9. 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 ( anyarray, anyelement ) → integer[]
array_positions(ARRAY['A','A','B','A'], 'A') → {1,2,4}

10. Prepends an element to the beginning of an array (same as the anycompatible || anycompatiblearray operator).

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

11. 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 ( anyarray, anyelement ) → anyarray
array_remove(ARRAY[1,2,3,2], 2) → {1,3}

12. Replaces each array element equal to the second argument with the third argument.

array_replace ( anyarray, anyelement, anyelement ) → anyarray
array_replace(ARRAY[1,2,5,4], 5, 3) → {1,2,3,4}

13. 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 anyarray, delimiter text [, null_string text ] ) → text
array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*') → 1,2,3,*,5

14. Returns the upper bound of the requested array dimension.

array_upper ( anyarray, integer ) → integer
array_upper(ARRAY[1,8,3,7], 1) → 4

15. Returns the total number of elements in the array, or 0 if the array is empty.

cardinality ( anyarray ) → integer
cardinality(ARRAY[[1,2],[3,4]]) → 4

16. Split the string when a delimiter occurs and form the remaining data into a text array. If the delimiter is NULL, each character in the string becomes a separate element in the array. If the delimiter is an empty string, the string is treated as a single field. If null_string is provided and is not NULL, fields matching that string will be converted to NULL entries.

string_to_array ( string text, delimiter text [, null_string text ] ) → text[]
string_to_array('xx~~yy~~zz', '~~', 'yy') → {xx,NULL,zz}

17. Expands an array into a set of rows. The array's elements are read out in storage order.

unnest ( anyarray ) → setof anyelement
unnest(ARRAY[1,2]) →
 1
 2

18. 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.

unnest ( anyarray, anyarray [, ... ] ) → setof anyelement, anyelement [, ... ]
select * from unnest(ARRAY[1,2], ARRAY['foo','bar','baz']) as x(a,b) →
 a |  b
---+-----
 1 | foo
 2 | bar
   | baz