Array Functions and Operators
Array Operators
- =
Description: Specifies whether two arrays are equal.
Example:
1 2 3 4 5
gaussdb=# SELECT ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] AS RESULT ; result -------- t (1 row)
- <>
Description: Specifies whether two arrays are not equal.
Example:
1 2 3 4 5
gaussdb=# SELECT ARRAY[1,2,3] <> ARRAY[1,2,4] AS RESULT; result -------- t (1 row)
- <
Description: Specifies whether an array is less than another.
Example:
1 2 3 4 5
gaussdb=# SELECT ARRAY[1,2,3] < ARRAY[1,2,4] AS RESULT; result -------- t (1 row)
- >
Description: Specifies whether an array is greater than another.
Example:
1 2 3 4 5
gaussdb=# SELECT ARRAY[1,4,3] > ARRAY[1,2,4] AS RESULT; result -------- t (1 row)
- <=
Description: Specifies whether an array is less than another.
Example:
1 2 3 4 5
gaussdb=# SELECT ARRAY[1,2,3] <= ARRAY[1,2,3] AS RESULT; result -------- t (1 row)
- >=
Description: Specifies whether an array is greater than or equal to another.
Example:
1 2 3 4 5
gaussdb=# SELECT ARRAY[1,4,3] >= ARRAY[1,4,3] AS RESULT; result -------- t (1 row)
- @>
Description: Specifies whether an array contains another.
Example:
1 2 3 4 5
gaussdb=# SELECT ARRAY[1,4,3] @> ARRAY[3,1] AS RESULT; result -------- t (1 row)
- <@
Description: Specifies whether an array is contained in another.
Example:
1 2 3 4 5
gaussdb=# SELECT ARRAY[2,7] <@ ARRAY[1,7,4,2,6] AS RESULT; result -------- t (1 row)
- &&
Description: Specifies whether an array overlaps another (have common elements).
Example:
1 2 3 4 5
gaussdb=# SELECT ARRAY[1,4,3] && ARRAY[2,1] AS RESULT; result -------- t (1 row)
- ||
Description: Array-to-array concatenation
Example:
1 2 3 4 5
gaussdb=# SELECT ARRAY[1,2,3] || ARRAY[4,5,6] AS RESULT; result --------------- {1,2,3,4,5,6} (1 row)
1 2 3 4 5
gaussdb=# SELECT ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]] AS RESULT; result --------------------------- {{1,2,3},{4,5,6},{7,8,9}} (1 row)
- ||
Description: Element-to-array concatenation
Example:
1 2 3 4 5
gaussdb=# SELECT 3 || ARRAY[4,5,6] AS RESULT; result ----------- {3,4,5,6} (1 row)
- ||
Description: Array-to-element concatenation
Example:
1 2 3 4 5
gaussdb=# SELECT ARRAY[4,5,6] || 7 AS RESULT; result ----------- {4,5,6,7} (1 row)
Array comparisons compare the array contents element-by-element, using the default B-tree comparison function for the element data type. In multidimensional arrays, the elements are accessed in row-major order. 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 Functions
- array_append(anyarray, anyelement)
Description: Appends an element to the end of an array, and only supports one-dimensional arrays.
Return type: anyarray
Example:
1 2 3 4 5
gaussdb=# SELECT array_append(ARRAY[1,2], 3) AS RESULT; result --------- {1,2,3} (1 row)
- array_prepend(anyelement, anyarray)
Description: Appends an element to the beginning of an array, and only supports one-dimensional arrays.
Return type: anyarray
Example:
1 2 3 4 5
gaussdb=# SELECT array_prepend(1, ARRAY[2,3]) AS RESULT; result --------- {1,2,3} (1 row)
- array_cat(anyarray, anyarray)
Description: Concatenates two arrays, and supports multi-dimensional arrays.
Return type: anyarray
Example:
1 2 3 4 5 6 7 8 9 10 11
gaussdb=# SELECT array_cat(ARRAY[1,2,3], ARRAY[4,5]) AS RESULT; result ------------- {1,2,3,4,5} (1 row) gaussdb=# SELECT array_cat(ARRAY[[1,2],[4,5]], ARRAY[6,7]) AS RESULT; result --------------------- {{1,2},{4,5},{6,7}} (1 row)
- array_union(anyarray, anyarray)
Description: Concatenates two arrays. Only one-dimensional arrays are supported. If an input parameter is NULL, another input parameter is returned.
Return type: anyarray
Example:
1 2 3 4 5 6 7 8 9 10 11
gaussdb=# SELECT array_union(ARRAY[1,2,3], ARRAY[3,4,5]) AS RESULT; result ------------- {1,2,3,3,4,5} (1 row) gaussdb=# SELECT array_union(ARRAY[1,2,3], NULL) AS RESULT; result --------- {1,2,3} (1 row)
- array_union_distinct(anyarray, anyarray)
Description: Concatenates two arrays and deduplicates them. Only one-dimensional arrays are supported. If an input parameter is NULL, another input parameter is returned.
Return type: anyarray
Example:
1 2 3 4 5 6 7 8 9 10 11
gaussdb=# SELECT array_union_distinct(ARRAY[1,2,3], ARRAY[3,4,5]) AS RESULT; result ------------- {1,2,3,4,5} (1 row) gaussdb=# SELECT array_union_distinct(ARRAY[1,2,3], NULL) AS RESULT; result --------- {1,2,3} (1 row)
- array_intersect(anyarray, anyarray)
Description: Intersects two arrays. Only one-dimensional arrays are supported. If any input parameter is NULL, NULL is returned.
Return type: anyarray
Example:
1 2 3 4 5 6 7 8 9 10 11
gaussdb=# SELECT array_intersect(ARRAY[1,2,3], ARRAY[3,4,5]) AS RESULT; result ------------- {3} (1 row) gaussdb=# SELECT array_intersect(ARRAY[1,2,3], NULL) AS RESULT; result -------- (1 row)
- array_intersect_distinct(anyarray, anyarray)
Description: Intersects two arrays and deduplicates them. Only one-dimensional arrays are supported. If any input parameter is NULL, NULL is returned.
Return type: anyarray
Example:
1 2 3 4 5 6 7 8 9 10 11
gaussdb=# SELECT array_intersect_distinct(ARRAY[1,2,2], ARRAY[2,2,4,5]) AS RESULT; result ------------- {2} (1 row) gaussdb=# SELECT array_intersect_distinct(ARRAY[1,2,3], NULL) AS RESULT; result -------- (1 row)
- array_except(anyarray, anyarray)
Description: Calculates the difference between two arrays. Only one-dimensional arrays are supported. If the first input parameter is NULL, NULL is returned. If the second input parameter is NULL, the first input parameter is returned.
Return type: anyarray
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
gaussdb=# SELECT array_except(ARRAY[1,2,3], ARRAY[3,4,5]) AS RESULT; result ------------- {1,2} (1 row) gaussdb=# SELECT array_except(ARRAY[1,2,3], NULL) AS RESULT; result --------- {1,2,3} (1 row) gaussdb=# SELECT array_except(NULL, ARRAY[3,4,5]) AS RESULT; result -------- (1 row)
- array_except_distinct(anyarray, anyarray)
Description: Calculates the difference between two arrays and deduplicates them. Only one-dimensional arrays are supported. If the first input parameter is NULL, NULL is returned. If the second input parameter is NULL, the first input parameter is returned.
Return type: anyarray
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
gaussdb=# SELECT array_except_distinct(ARRAY[1,2,2,3], ARRAY[3,4,5]) AS RESULT; result ------------- {1,2} (1 row) gaussdb=# SELECT array_except_distinct(ARRAY[1,2,3], NULL) AS RESULT; result --------- {1,2,3} (1 row) gaussdb=# SELECT array_except_distinct(NULL, ARRAY[3,4,5]) AS RESULT; result -------- (1 row)
- array_ndims(anyarray)
Description: Returns the number of dimensions of an array.
Return type: int
Example:
1 2 3 4 5
gaussdb=# SELECT array_ndims(ARRAY[[1,2,3], [4,5,6]]) AS RESULT; result -------- 2 (1 row)
- array_dims(anyarray)
Description: Returns the low-order flag bits and high-order flag bits of each dimension in an array.
Return type: text
Example:
1 2 3 4 5
gaussdb=# SELECT array_dims(ARRAY[[1,2,3], [4,5,6]]) AS RESULT; result ------------ [1:2][1:3] (1 row)
- array_length(anyarray, int)
Description: Returns the length of the requested array dimension. int is the requested array dimension.
Return type: int
Example:
1 2 3 4 5 6 7 8 9 10 11
gaussdb=# SELECT array_length(array[1,2,3], 1) AS RESULT; result -------- 3 (1 row) gaussdb=# SELECT array_length(array[[1,2,3],[4,5,6]], 2) AS RESULT; result -------- 3 (1 row)
- array_lower(anyarray, int)
Description: Returns lower bound of the requested array dimension. int is the requested array dimension.
Return type: int
Example:
1 2 3 4 5
gaussdb=# SELECT array_lower('[0:2]={1,2,3}'::int[], 1) AS RESULT; result -------- 0 (1 row)
If the first parameter is null, error "Reference to uninitialized collection" is reported after varray_compat is enabled. Before varray_compat is enabled, null is returned.
- array_sort(anyarray)
Description: Returns an array in ascending order. Only one-dimensional array of the anyarray type supports sorting. Multidimensional arrays return NULL. Currently, the record, XML, XMLtype, and JSON arrays cannot be sorted.
Return type: anyarray
Example:
1 2 3 4 5 6 7 8 9 10
gaussdb=# SELECT array_sort(ARRAY[5,1,3,6,2,7]) AS RESULT; result ------------- {1,2,3,5,6,7} (1 row) gaussdb=# SELECT array_sort(array[array[1,23], array[1,34]]); array_sort ------------- {NULL,NULL} (1 row)
- array_upper(anyarray, int)
Description: Returns upper bound of the requested array dimension. int is the requested array dimension.
Return type: int
Example:
1 2 3 4 5
gaussdb=# SELECT array_upper(ARRAY[1,8,3,7], 1) AS RESULT; result -------- 4 (1 row)
If the first parameter is null, error "Reference to uninitialized collection" is reported after varray_compat is enabled. Before varray_compat is enabled, null is returned.
- array_to_string(anyarray, text [, text])
Description: Uses the first text as the new delimiter and the second text to replace NULL values.
Return type: text
Example:
1 2 3 4 5
gaussdb=# SELECT array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*') AS RESULT; result ----------- 1,2,3,*,5 (1 row)
- array_delete(anyarray)
Description: Clears elements in an array and returns an empty array of the same type.
Return type: anyarray
Example:
gaussdb=# SELECT array_delete(ARRAY[1,8,3,7]) AS RESULT; result -------- {} (1 row)
If the first parameter is null, error "Reference to uninitialized collection" is reported after varray_compat is enabled. Before varray_compat is enabled, null is returned.
- array_deleteidx(anyarray, int)
Description: Deletes specified index elements from an array and returns an array consisting of the remaining elements.
Return type: anyarray
Example:
gaussdb=# SELECT array_deleteidx(ARRAY[1,2,3,4,5], 1) AS RESULT; result ----------- {2,3,4,5} (1 row)
- array_deleteidx(anyarray, int): This function is disabled when the value of a_format_version is 10c and the value of a_format_dev_version is s1.
- After the varray_compat parameter is enabled, if the first parameter is null, error "Reference to uninitialized collection" is reported. If the second parameter is null, the original array is returned. If this parameter is disabled and one of the parameters is null, null is returned. If the value of the second parameter is less than or equal to 0, the error "Subscript outside of limit" is reported after the parameter is enabled. Before the parameter is enabled, the original array is returned. If the value of the second parameter is greater than the number of elements in the array (including 0, that is, an empty array), the error "Subcript outside of count" is reported after the parameter is enabled. Before the parameter is enabled, the original array is returned.
- array_extendnull(anyarray, int)
Description: Adds a specified number of NULL elements to the end of an array.
Return type: anyarray
Example:
gaussdb=# SELECT array_extendnull(ARRAY[1,8,3,7],1) AS RESULT; result -------------- {1,8,3,7,null} (1 row)
If the first parameter is null, error "Reference to uninitialized collection" is reported after varray_compat is enabled. Before varray_compat is enabled, null is returned. If the second parameter is null, null is returned before varray_compat is enabled. After varray_compat is enabled, the original array is returned. If the second parameter is less than 0, error "numeric or value error" is reported after varray_compat is enabled. Before varray_compat is enabled, the original array is returned.
- array_extendnull(anyarray, int, int)
Description: Adds a specified number of elements with a specified index to the end of an array.
Return type: anyarray
Example:
gaussdb=# SELECT array_extendnull(ARRAY[1,8,3,7],2,2) AS RESULT; result -------------- {1,8,3,7,8,8} (1 row)
array_extendnull(anyarray, int, int): This function takes effect when the value of a_format_version is 10c and the value of a_format_dev_version is s1.
If the first parameter is null, error "Reference to uninitialized collection" is reported after varray_compat is enabled. Before varray_compat is enabled, null is returned. If the second or third parameter is null, null is returned before varray_compat is enabled. After varray_compat is enabled, the original array is returned.
- array_trim(anyarray, int)
Description: Deletes a specified number of elements from the end of an array.
Return type: anyarray
Example:
gaussdb=# SELECT array_trim(ARRAY[1,8,3,7],1) AS RESULT; result --------- {1,8,3} (1 row)
If the first parameter is null, error "Reference to uninitialized collection" is reported after varray_compat is enabled and null is returned before varray_compat is enabled. If the second parameter is null, null is returned before varray_compat is enabled and the original array is returned after varray_compat is enabled. If the second parameter exceeds the number of array elements (including 0, that is, an empty array), error "Subscipt outside of count" is reported after varray_compat is enabled, and before varray_compat is enabled, an empty array is returned. If the value of the second parameter is less than 0, an error message "numeric or value error" is displayed after varray_compat is enabled. Before varray_compat is enabled, the original array is returned.
- array_exists(anyarray, int)
Description: Checks whether the second parameter is a valid index of an array.
Return type: Boolean
Example:
gaussdb=# SELECT array_exists(ARRAY[1,8,3,7],1) AS RESULT; result -------- t (1 row) - array_next(anyarray, int)
Description: Returns the index of the element following a specified index in an array based on the second input parameter.
Return type: int
Example:
gaussdb=# SELECT array_next(ARRAY[1,8,3,7],1) AS RESULT; result -------- 2 (1 row)
If the first parameter is null, error "Reference to uninitialized collection" is reported after varray_compat is enabled. Before varray_compat is enabled, null is returned.
- array_prior(anyarray, int)
Description: Returns the index of the element followed by a specified index in an array based on the second input parameter.
Return type: int
Example:
gaussdb=# SELECT array_prior(ARRAY[1,8,3,7],2) AS RESULT; result -------- 1 (1 row)
If the first parameter is null, error "Reference to uninitialized collection" is reported after varray_compat is enabled. Before varray_compat is enabled, null is returned.
- string_to_array(text, text, [text])
Description: Uses the second text as the new delimiter and the third text as the substring to be replaced by NULL values. A substring can be replaced by NULL values only when it is the same as the third text.
Return type: text[]
Example:
1 2 3 4 5 6 7 8 9 10
gaussdb=# SELECT string_to_array('xx~^~yy~^~zz', '~^~', 'yy') AS RESULT; result -------------- {xx,NULL,zz} (1 row) gaussdb=# SELECT string_to_array('xx~^~yy~^~zz', '~^~', 'y') AS RESULT; result ------------ {xx,yy,zz} (1 row)
- unnest(anyarray)
Description: Expands an array to a set of rows.
Return type: setof anyelement
Example:
1 2 3 4 5 6
gaussdb=# SELECT unnest(ARRAY[1,2]) AS RESULT; result -------- 1 2 (2 rows)
- unnest(anynesttable)
Description: Returns a collection of elements in a nest-table.
Return type: setof anyelement
Restriction: The tableof type cannot be nested with the tableof type, or the tableof type cannot be nested with other types and then the tableof type.
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
CREATE OR REPLACE PROCEDURE f1() AS TYPE t1 IS TABLE of INT; v2 t1 := t1(null, 2, 3, 4, null); tmp INT; CURSOR c1 IS SELECT * FROM unnest(v2); BEGIN OPEN c1; FOR i IN 1 .. v2.count LOOP FETCH c1 INTO tmp; IF tmp IS null THEN dbe_output.print_line(i || ': is null'); ELSE dbe_output.print_line(i || ': ' || tmp); END IF; END LOOP; CLOSE c1; END; / gaussdb=# CALL f1(); 1: is null 2: 2 3: 3 4: 4 5: is null f1 ---- (1 row)
- unnest(anyindexbytable)
Description: Returns the collection of elements in an index-by table sorted by index.
Return type: setof anyelement
Restriction: The tableof type cannot be nested with the tableof type, or the tableof type cannot be nested with other types and then the tableof type. Only the index by int type is supported. The index by varchar type is not supported.
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
CREATE OR REPLACE PROCEDURE f1() AS TYPE t1 IS TABLE of INT INDEX BY INT; v2 t1 := t1(1=>1, -10=>(-10), 6=>6, 4=>null); tmp INT; CURSOR c1 IS SELECT * FROM unnest(v2); BEGIN OPEN c1; FOR i IN 1 .. v2.count LOOP FETCH c1 INTO tmp; IF tmp IS null THEN dbe_output.print_line(i || ': is null'); ELSE dbe_output.print_line(i || ': ' || tmp); END IF; END LOOP; CLOSE c1; END; / gaussdb=# CALL f1(); 1: -10 2: 1 3: is null 4: 6 f1 ---- (1 row)
In string_to_array, if the delimiter parameter is NULL, each character in the input string will become a separate element in the resulting array. If the delimiter is an empty string, then the entire input string is returned as a one-element array. Otherwise the input string is split at each occurrence of the delimiter string.
In string_to_array, if the null-string parameter is omitted or NULL, none of the substrings of the input will be replaced by NULL.
In array_to_string, if the null-string parameter is omitted or NULL, any null elements in the array are simply skipped and not represented in the output string.
- _pg_keysequal
Description: Checks whether two smallint arrays are the same.
Parameter: smallint[], smallint[]
Return type: Boolean
This function exists in the information_schema namespace.
- cardinality(anyarray)
Description: Returns the total number of elements in each dimension of an array. If the array is empty, 0 is returned.
Return type: integer
Example:
gaussdb=# SELECT cardinality(array[[1, 2], [3, 4]]); cardinality -------- 4 (1 row) - array_positions(anyarray, anyelement)
Description: Returns an array of indexes of all second parameters that appear in the array given as the first parameter.
Return type: int[]
Example:
gaussdb=# SELECT array_positions(array[1, 2, 3, 1], 1) AS RESULT; result -------- {1,4} (1 row)
- The array must be one-dimensional.
- The second parameter can be set to NULL.
- If no second parameter is found in the array, an empty array is returned.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot
