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. (Multidimensional arrays are supported.)

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 any input parameter is null, the function will return the other input parameter.

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 any input parameter is null, the function will return the other input parameter.

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, the function will return null.

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, the function will return null.

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, the function will return null. If the second input parameter is null, the function will return the first input parameter.

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, the function will return null. If the second input parameter is null, the function will return the first input parameter.

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 text representation of array dimensions.

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 array dimensions.

Return type: int

Example:

1
2
3
4
5

gaussdb=# SELECT array_length(array[1,2,3], 1) AS RESULT;
 result 
--------
      3
(1 row)

array_lower(anyarray, int)

Description: Returns the lower bound of array dimensions.

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)

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 the upper bound of array dimensions.

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)

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)

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 a_format_version is 10c and a_format_dev_version is s1.

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)

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)

The array_extendnull(anyarray, int, int) function takes effect when a_format_version is 10c and a_format_dev_version is s1.

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)

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)

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)

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)

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.

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)

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