From 4e23c9ef65accde7eb3e56aa28d50ae5cf79b64b Mon Sep 17 00:00:00 2001 From: Andrew Dunstan Date: Mon, 4 Aug 2025 08:56:48 -0400 Subject: Split func.sgml into more manageable pieces func.sgml has grown over the years to the point where it is very difficult to manage. This commit splits out each sect1 piece into its own file, which is then included in the main file, so that the built documentation should be identical to the pre-split documentation. All these new files are placed in a new "func" subdirectory, and the previous func.sgml is removed. Done using scripts developed by: Author: jian he Discussion: https://postgr.es/m/CACJufxFgAh1--EMwOjMuANe=VTmjkNaZjH+AzSe04-8ZCGiESA@mail.gmail.com --- doc/src/sgml/func/func-array.sgml | 646 ++++++++++++++++++++++++++++++++++++++ 1 file changed, 646 insertions(+) create mode 100644 doc/src/sgml/func/func-array.sgml (limited to 'doc/src/sgml/func/func-array.sgml') diff --git a/doc/src/sgml/func/func-array.sgml b/doc/src/sgml/func/func-array.sgml new file mode 100644 index 00000000000..97e4865a5f7 --- /dev/null +++ b/doc/src/sgml/func/func-array.sgml @@ -0,0 +1,646 @@ + + Array Functions and Operators + + + shows the specialized operators + available for array types. + In addition to those, the usual comparison operators shown in 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. + + + + Array Operators + + + + + Operator + + + Description + + + Example(s) + + + + + + + + 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, + thus ARRAY[1] and ARRAY[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 + + + + + + anycompatiblearray || anycompatiblearray + anycompatiblearray + + + 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 ). + + + 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.9]] + {{1,2,3},{4,5,6},{7,8,9.9}} + + + + + + anycompatible || anycompatiblearray + anycompatiblearray + + + 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} + + + + + + anycompatiblearray || anycompatible + anycompatiblearray + + + 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} + + + + +
+ + + See for more details about array operator + behavior. See for more details about + which operators support indexed operations. + + + + shows the functions + available for use with array types. See + for more information and examples of the use of these functions. + + + + Array Functions + + + + + Function + + + Description + + + Example(s) + + + + + + + + + array_append + + array_append ( anycompatiblearray, anycompatible ) + anycompatiblearray + + + Appends an element to the end of an array (same as + the anycompatiblearray || anycompatible + operator). + + + array_append(ARRAY[1,2], 3) + {1,2,3} + + + + + + + array_cat + + array_cat ( anycompatiblearray, anycompatiblearray ) + anycompatiblearray + + + Concatenates two arrays (same as + the anycompatiblearray || anycompatiblearray + operator). + + + array_cat(ARRAY[1,2,3], ARRAY[4,5]) + {1,2,3,4,5} + + + + + + + array_dims + + 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 + + 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 all 1). + + + 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 + + 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 + + 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 + + array_ndims ( anyarray ) + integer + + + Returns the number of dimensions of the array. + + + array_ndims(ARRAY[[1,2,3], [4,5,6]]) + 2 + + + + + + + array_position + + array_position ( anycompatiblearray, anycompatible , integer ) + integer + + + 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(ARRAY['sun', 'mon', 'tue', 'wed', 'thu', 'fri', 'sat'], 'mon') + 2 + + + + + + + array_positions + + array_positions ( anycompatiblearray, anycompatible ) + 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 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(ARRAY['A','A','B','A'], 'A') + {1,2,4} + + + + + + + array_prepend + + array_prepend ( anycompatible, anycompatiblearray ) + anycompatiblearray + + + Prepends an element to the beginning of an array (same as + the anycompatible || anycompatiblearray + operator). + + + array_prepend(1, ARRAY[2,3]) + {1,2,3} + + + + + + + array_remove + + array_remove ( anycompatiblearray, anycompatible ) + anycompatiblearray + + + 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 NULLs. + + + array_remove(ARRAY[1,2,3,2], 2) + {1,3} + + + + + + + array_replace + + array_replace ( anycompatiblearray, anycompatible, anycompatible ) + anycompatiblearray + + + 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_reverse + + array_reverse ( anyarray ) + anyarray + + + Reverses the first dimension of the array. + + + array_reverse(ARRAY[[1,2],[3,4],[5,6]]) + {{5,6},{3,4},{1,2}} + + + + + + + array_sample + + array_sample ( array anyarray, n integer ) + anyarray + + + Returns an array of n items randomly selected + from array. n may not + exceed the length of array's first dimension. + If array is multi-dimensional, + an item is a slice having a given first subscript. + + + array_sample(ARRAY[1,2,3,4,5,6], 3) + {2,6,1} + + + array_sample(ARRAY[[1,2],[3,4],[5,6]], 2) + {{5,6},{1,2}} + + + + + + + array_shuffle + + array_shuffle ( anyarray ) + anyarray + + + Randomly shuffles the first dimension of the array. + + + array_shuffle(ARRAY[[1,2],[3,4],[5,6]]) + {{5,6},{1,2},{3,4}} + + + + + + + array_sort + + array_sort ( + array anyarray + , descending boolean + , nulls_first boolean + ) + anyarray + + + Sorts the first dimension of the array. + The sort order is determined by the default sort ordering of the + array's element type; however, if the element type is collatable, + the collation to use can be specified by adding + a COLLATE clause to + the array argument. + + + If descending is true then sort in + descending order, otherwise ascending order. If omitted, the + default is ascending order. + If nulls_first is true then nulls appear + before non-null values, otherwise nulls appear after non-null + values. + If omitted, nulls_first is taken to have + the same value as descending. + + + array_sort(ARRAY[[2,4],[2,1],[6,5]]) + {{2,1},{2,4},{6,5}} + + + + + + + array_to_string + + array_to_string ( array anyarray, delimiter text , null_string text ) + text + + + 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. + See also string_to_array. + + + array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*') + 1,2,3,*,5 + + + + + + + array_upper + + array_upper ( anyarray, integer ) + integer + + + Returns the upper bound of the requested array dimension. + + + array_upper(ARRAY[1,8,3,7], 1) + 4 + + + + + + + cardinality + + 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 + + + + + + + trim_array + + trim_array ( array anyarray, n integer ) + anyarray + + + Trims an array by removing the last n elements. + If the array is multidimensional, only the first dimension is trimmed. + + + trim_array(ARRAY[1,2,3,4,5,6], 2) + {1,2,3,4} + + + + + + + unnest + + 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 with NULLs. This form is only allowed + in a query's FROM clause; see . + + + select * from unnest(ARRAY[1,2], ARRAY['foo','bar','baz']) as x(a,b) + + + a | b +---+----- + 1 | foo + 2 | bar + | baz + + + + + +
+ + + See also about the aggregate + function array_agg for use with arrays. + + -- cgit v1.2.3