Help Center > > Developer Guide> SQL Reference> Functions and Operators> Array Functions and Operators

Array Functions and Operators

Updated at: Jul 15, 2020 GMT+08:00

Array Operators

  • =

    Description: Specifies whether two arrays are equal.

    For example:

    1
    2
    3
    4
    5
    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.

    For example:

    1
    2
    3
    4
    5
    SELECT ARRAY[1,2,3] <> ARRAY[1,2,4] AS RESULT;
     result 
    --------
     t
    (1 row)
    
  • <

    Description: Specifies whether an array is less than another.

    For example:

    1
    2
    3
    4
    5
    SELECT ARRAY[1,2,3] < ARRAY[1,2,4] AS RESULT;
     result 
    --------
     t
    (1 row)
    
  • >

    Description: Specifies whether an array is greater than another.

    For example:

    1
    2
    3
    4
    5
    SELECT ARRAY[1,4,3] > ARRAY[1,2,4] AS RESULT;
     result 
    --------
     t
    (1 row)
    
  • <=

    Description: Specifies whether an array is less than another.

    For example:

    1
    2
    3
    4
    5
    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.

    For example:

    1
    2
    3
    4
    5
    SELECT ARRAY[1,4,3] >= ARRAY[1,4,3] AS RESULT;
     result 
    --------
     t
    (1 row)
    
  • @>

    Description: Specifies whether an array contains another.

    For example:

    1
    2
    3
    4
    5
    SELECT ARRAY[1,4,3] @> ARRAY[3,1] AS RESULT;
     result 
    --------
     t
    (1 row)
    
  • <@

    Description: Specifies whether an array is contained in another.

    For example:

    1
    2
    3
    4
    5
    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).

    For example:

    1
    2
    3
    4
    5
    SELECT ARRAY[1,4,3] && ARRAY[2,1] AS RESULT;
     result 
    --------
     t
    (1 row)
    
  • ||

    Description: Array-to-array concatenation

    For example:

    1
    2
    3
    4
    5
    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
    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

    For example:

    1
    2
    3
    4
    5
    SELECT 3 || ARRAY[4,5,6] AS RESULT;
      result   
    -----------
     {3,4,5,6}
    (1 row)
    
  • ||

    Description: Array-to-element concatenation

    For example:

    1
    2
    3
    4
    5
    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 dimension-1 arrays.

    Return type: anyarray

    For example:

    1
    2
    3
    4
    5
    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 dimension-1 arrays.

    Return type: anyarray

    For example:

    1
    2
    3
    4
    5
    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

    For example:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    SELECT array_cat(ARRAY[1,2,3], ARRAY[4,5]) AS RESULT;
       result    
    -------------
     {1,2,3,4,5}
    (1 row)
    
    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 the array.

    Return type: int

    For example:

    1
    2
    3
    4
    5
    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's dimensions.

    Return type: text

    For example:

    1
    2
    3
    4
    5
    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.

    Return type: int

    For example:

    1
    2
    3
    4
    5
    SELECT array_length(array[1,2,3], 1) AS RESULT;
     result 
    --------
          3
    (1 row)
    
  • array_lower(anyarray, int)

    Description: Returns lower bound of the requested array dimension.

    Return type: int

    For example:

    1
    2
    3
    4
    5
    SELECT array_lower('[0:2]={1,2,3}'::int[], 1) AS RESULT;
     result 
    --------
          0
    (1 row)
    
  • array_upper(anyarray, int)

    Description: Returns upper bound of the requested array dimension.

    Return type: int

    For example:

    1
    2
    3
    4
    5
    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

    For example:

    1
    2
    3
    4
    5
    SELECT array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*') AS RESULT;
      result   
    -----------
     1,2,3,*,5
    (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[]

    For example:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    SELECT string_to_array('xx~^~yy~^~zz', '~^~', 'yy') AS RESULT;
        result    
    --------------
     {xx,NULL,zz}
    (1 row)
    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

    For example:

    1
    2
    3
    4
    5
    6
    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.

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel