Array Functions
array_append(anyarray, anyelement)
Description: Appends an element to the end of an array and supports only one-dimensional arrays.
Return type: anyarray.
Example:
1 2 3 4 5 |
postgres=#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 supports only one-dimensional arrays.
Return type: anyarray.
Example:
1 2 3 4 5 |
postgres=#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 |
postgres=#SELECT array_cat(ARRAY[1,2,3], ARRAY[4,5]) AS RESULT; result ------------- {1,2,3,4,5} (1 row) postgres=#SELECT array_cat(ARRAY[[1,2],[4,5]], ARRAY[6,7]) AS RESULT; result --------------------- {{1,2},{4,5},{6,7}} (1 row) |
array_ndims(anyarray)
Description: Returns the number of dimensions of an array.
Return type: int.
Example:
1 2 3 4 5 |
postgres=#SELECT array_ndims(ARRAY[[1,2,3], [4,5,6]]) AS RESULT; result -------- 2 (1 row) |
array_dims(anyarray)
Description: Returns a textual representation of the array's dimensions.
Return type: text.
Example:
1 2 3 4 5 |
postgres=#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 specified array dimension.
Return type: int.
Example:
1 2 3 4 5 |
postgres=#SELECT array_length(array[1,2,3], 1) AS RESULT; result -------- 3 (1 row) |
array_lower(anyarray, int)
Description: Returns the lower bound of the specified array dimension.
Return type: int.
Example:
1 2 3 4 5 |
postgres=#SELECT array_lower('[0:2]={1,2,3}'::int[], 1) AS RESULT; result -------- 0 (1 row) |
array_upper(anyarray, int)
Description: Returns the upper bound of the specified array dimension.
Return type: int.
Example:
1 2 3 4 5 |
postgres=#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 for the array and the second text to substitute for null values in the array.
Return type: text.
Example:
1 2 3 4 5 |
postgres=#SELECT array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*') AS RESULT; result ----------- 1,2,3,*,5 (1 row) |

In string_to_array, if the null string parameter is omitted or is NULL, substrings in the string without input content are replaced with NULL.
In array_to_string, if the null string parameter is omitted or is NULL, any null elements in the array are skipped during processing and do not appear in the output string.
string_to_array(text, text [, text])
Description: Uses the second text to specify a delimiter and the third optional text as a template for replacing NULL values. If a split substring exactly matches the third optional text, it is replaced with NULL.
Return type: text[].
Example:
1 2 3 4 5 6 7 8 9 10 |
postgres=#SELECT string_to_array('xx~^~yy~^~zz', '~^~', 'yy') AS RESULT; result -------------- {xx,NULL,zz} (1 row) postgres=#SELECT string_to_array('xx~^~yy~^~zz', '~^~', 'y') AS RESULT; result ------------ {xx,yy,zz} (1 row) |

In string_to_array, if the delimiter parameter is NULL, each character in the input string becomes a separate element in the resulting array. If the delimiter is an empty string, the entire input string becomes a single-element array. Otherwise, the input string is split at each occurrence of the delimiter string.
unnest(anyarray)
Description: Expands an array into a set of rows.
Return type: setof anyelement.
Example:
1 2 3 4 5 6 |
postgres=#SELECT unnest(ARRAY[1,2]) AS RESULT; result -------- 1 2 (2 rows) |
The unnest function is used in conjunction with the string_to_array function. To transform an array into columns, first split the string into an array using commas, then expand the array into columns:
1 2 3 4 5 6 7 8 |
SELECT unnest(string_to_array('a,b,c,d',',')) AS RESULT; result -------- a b c d (4 rows) |
interval(N, N1, N2, N3 ... )
Description: Searches the input integer array and returns the last array index that is less than or equal to the target parameter n. If n is NULL, -1 is returned. The interval function does not support the interval(N, N1) scenario. This function is only supported by clusters of version 8.2.0 or later.
Return type: int.
Example:
1 2 3 4 5 |
postgres=#SELECT INTERVAL(10, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 10, 11) AS RESULT; result -------- 11 (1 row) |
split(string, delim)
Description: Splits the string according to the delimiter and returns an array. This function is only supported by clusters of version 8.2.0 or later.
Return type: text[].
Example:
1 2 3 4 5 6 7 8 9 10 |
postgres=#SELECT SPLIT('a-b-c-d-e', '-') AS RESULT; result ------------- {a,b,c,d,e} (1 row) postgres=#SELECT SPLIT('a-b-c-d-e', '-')[4] AS RESULT; result -------- d (1 row) |
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