Array Functions and Operators
Array Operators
- =
Description: Specifies whether two arrays are equal.
Example:
1 2 3 4 5
openGauss=# 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
openGauss=# 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
openGauss=# 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
openGauss=# SELECT ARRAY[1,4,3] > ARRAY[1,2,4] AS RESULT; result -------- t (1 row)
- <=
Description: Specifies whether an array is less than or equal to another.
Example:
1 2 3 4 5
openGauss=# 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
openGauss=# 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
openGauss=# 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
openGauss=# 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
openGauss=# SELECT ARRAY[1,4,3] && ARRAY[2,1] AS RESULT; result -------- t (1 row)
- ||
Description: Specifies array-to-array concatenation.
Example:
1 2 3 4 5
openGauss=# 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
openGauss=# 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: Specifies element-to-array concatenation.
Example:
1 2 3 4 5
openGauss=# SELECT 3 || ARRAY[4,5,6] AS RESULT; result ----------- {3,4,5,6} (1 row)
- ||
Description: Specifies array-to-element concatenation.
Example:
1 2 3 4 5
openGauss=# 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 number of dimensions 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. Only one-dimensional arrays are supported.
Return type: anyarray
Example:
1 2 3 4 5
openGauss=# 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. Only one-dimensional arrays are supported.
Return type: anyarray
Example:
1 2 3 4 5
openGauss=# 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
openGauss=# SELECT array_cat(ARRAY[1,2,3], ARRAY[4,5]) AS RESULT; result ------------- {1,2,3,4,5} (1 row) openGauss=# 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.
Return type: anyarray
Example:
1 2 3 4 5
openGauss=# SELECT array_union(ARRAY[1,2,3], ARRAY[3,4,5]) AS RESULT; result ------------- {1,2,3,3,4,5} (1 row)
- array_union_distinct(anyarray, anyarray)
Description: Concatenates two arrays and deduplicates them. Only one-dimensional arrays are supported.
Return type: anyarray
Example:
1 2 3 4 5
openGauss=# SELECT array_union_distinct(ARRAY[1,2,3], ARRAY[3,4,5]) AS RESULT; result ------------- {1,2,3,4,5} (1 row)
- array_intersect(anyarray, anyarray)
Description: Intersects two arrays. Only one-dimensional arrays are supported.
Return type: anyarray
Example:
1 2 3 4 5
openGauss=# SELECT array_intersect(ARRAY[1,2,3], ARRAY[3,4,5]) AS RESULT; result ------------- {3} (1 row)
- array_intersect_distinct(anyarray, anyarray)
Description: Intersects two arrays and deduplicates them. Only one-dimensional arrays are supported.
Return type: anyarray
Example:
1 2 3 4 5
openGauss=# SELECT array_intersect_distinct(ARRAY[1,2,2], ARRAY[2,2,4,5]) AS RESULT; result ------------- {2} (1 row)
- array_except(anyarray, anyarray)
Description: Calculates the difference between two arrays. Only one-dimensional arrays are supported.
Return type: anyarray
Example:
1 2 3 4 5
openGauss=# SELECT array_except(ARRAY[1,2,3], ARRAY[3,4,5]) AS RESULT; result ------------- {1,2} (1 row)
- array_except_distinct(anyarray, anyarray)
Description: Calculates the difference between two arrays and deduplicates them. Only one-dimensional arrays are supported.
Return type: anyarray
Example:
1 2 3 4 5
openGauss=# SELECT array_except_distinct(ARRAY[1,2,2,3], ARRAY[3,4,5]) AS RESULT; result ------------- {1,2} (1 row)
- array_ndims(anyarray)
Description: Returns the number of dimensions of an array.
Return type: int
Example:
1 2 3 4 5
openGauss=# SELECT array_ndims(ARRAY[[1,2,3], [4,5,6]]) AS RESULT; result -------- 2 (1 row)
- array_dims(anyarray)
Description: Returns a text representation of array dimensions.
Return type: text
Example:
1 2 3 4 5
openGauss=# 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 array dimensions.
Return type: int
Example:
1 2 3 4 5
openGauss=# SELECT array_length(array[1,2,3], 1) AS RESULT; result -------- 3 (1 row)
- array_lower(anyarray, int)
Description: Returns lower bound of the array dimensions.
Return type: int
Example:
1 2 3 4 5
openGauss=# SELECT array_lower('[0:2]={1,2,3}'::int[], 1) AS RESULT; result -------- 0 (1 row)
- array_sort(anyarray)
Description: Returns an array in ascending order.
Return type: anyarray
Example:
1 2 3 4 5
openGauss=# SELECT array_sort(ARRAY[5,1,3,6,2,7]) AS RESULT; result ------------- {1,2,3,5,6,7} (1 row)
- array_upper(anyarray, int)
Description: Returns upper bound of the array dimensions.
Return type: int
Example:
1 2 3 4 5
openGauss=# SELECT array_upper(ARRAY[1,8,3,7], 1) AS RESULT; result -------- 4 (1 row)
- 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
openGauss=# 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:
openGauss=# SELECT array_delete(ARRAY[1,8,3,7]) AS RESULT; result -------- {} (1 row) - array_deleteidx(anyarray, int)
Description: Deletes specified subscript elements from an array and returns an array consisting of the remaining elements.
Return type: anyarray
Example:
openGauss=# SELECT array_deleteidx(ARRAY[1,2,3,4,5], 1) AS RESULT; result ----------- {2,3,4,5} (1 row) - array_extendnull(anyarray, int)
Description: Adds a specified number of null elements to the end of an array.
Return type: anyarray
Example:
openGauss=# SELECT array_extend(ARRAY[1,8,3,7],1) AS RESULT; result -------------- {1,8,3,7,null} (1 row) - array_trim(anyarray, int)
Description: Deletes a specified number of elements from the end of an array.
Return type: anyarray
Example:
openGauss=# SELECT array_trim(ARRAY[1,8,3,7],1) AS RESULT; result --------- {1,8,3} (1 row) - array_exists(anyarray, int)
Description: Checks whether the second parameter is a valid subscript of an array.
Return type: Boolean
Example:
openGauss=# SELECT array_exists(ARRAY[1,8,3,7],1) AS RESULT; result -------- t (1 row)
- array_next(anyarray, int)
Description: Returns the subscript of the element following a specified subscript in an array based on the second input parameter.
Return type: int
Example:
openGauss=# SELECT array_next(ARRAY[1,8,3,7],1) AS RESULT; result -------- 2 (1 row) - array_prior(anyarray, int)
Description: Returns the subscript of the element followed by a specified subscript in an array based on the second input parameter.
Return type: int
Example:
openGauss=# SELECT array_prior(ARRAY[1,8,3,7],2) AS RESULT; result -------- 1 (1 row)
- 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
openGauss=# SELECT string_to_array('xx~^~yy~^~zz', '~^~', 'yy') AS RESULT; result -------------- {xx,NULL,zz} (1 row) openGauss=# 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
openGauss=# SELECT unnest(ARRAY[1,2]) AS RESULT; result -------- 1 2 (2 rows)
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.
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
