Movatterモバイル変換

anyarray@>anyarray →boolean

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, thusARRAY[1] andARRAY[1,1] are each considered to contain the other.)

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

anyarray<@anyarray →boolean

Is the first array contained by the second?

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

anyarray&&anyarray →boolean

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

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

anyarray||anyarray →anyarray

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

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

anyelement||anyarray →anyarray

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

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

anyarray||anyelement →anyarray

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

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

array_append (anyarray,anyelement ) →anyarray

Appends an element to the end of an array (same as theanyarray||anyelement operator).

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

array_cat (anyarray,anyarray ) →anyarray

Concatenates two arrays (same as theanyarray||anyarray 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, having dimensions of the lengths specified by the second argument. The optional third argument supplies lower-bound values for each dimension (which default to all1).

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. (Produces NULL instead of 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, orNULL 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 usingIS NOT DISTINCT FROM semantics, so it is possible to search forNULL.

array_position(ARRAY['sun', 'mon', 'tue', 'wed', 'thu', 'fri', 'sat'], 'mon') →2

array_positions (anyarray,anyelement ) →integer[]

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 usingIS NOT DISTINCT FROM semantics, so it is possible to search forNULL.NULL is returned only if the array isNULL; if the value is not found in the array, an empty array is returned.

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 (same as theanyelement||anyarray 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. Comparisons are done usingIS NOT DISTINCT FROM semantics, so it is possible to removeNULLs.

array_remove(ARRAY[1,2,3,2], 2) →{1,3}

array_replace (anyarray,anyelement,anyelement ) →anyarray

Replaces each array 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 (arrayanyarray,delimitertext [,null_stringtext] ) →text

Converts each array element to its text representation, and concatenates those separated by thedelimiter string. Ifnull_string is given and is notNULL, thenNULL array entries are represented by that string; otherwise, they are omitted.

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 (stringtext,delimitertext [,null_stringtext] ) →text[]

Splits thestring at occurrences ofdelimiter and forms the remaining data into atext array. Ifdelimiter isNULL, each character in thestring will become a separate element in the array. Ifdelimiter is an empty string, then thestring is treated as a single field. Ifnull_string is supplied and is notNULL, fields matching that string are converted toNULL entries.

string_to_array('xx~~yy~~zz', '~~', 'yy') →{xx,NULL,zz}

unnest (anyarray ) →setof anyelement

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

unnest(ARRAY[1,2]) →

 1 2

unnest(ARRAY[['foo','bar'],['baz','quux']]) →

 foo bar baz quux

unnest (anyarray,anyarray [, ...] ) →setof anyelement, anyelement [, ... ]

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 withNULLs. This form is only allowed in a query's FROM clause; seeSection 7.2.1.4.

select * from unnest(ARRAY[1,2], ARRAY['foo','bar','baz']) as x(a,b) →

 a |  b---+----- 1 | foo 2 | bar   | baz