Help Center/ DataArts Fabric/ Developer Guide/ SQL Syntax Reference/ Functions and Operators/ Array Functions and Operators/ Array Operators
Updated on 2025-08-25 GMT+08:00
View PDF
Share

Array Operators

Array comparison uses the default B-tree comparison function to compare all elements one by one. Elements of multi-dimensional arrays are accessed in row-major order. If the contents of two arrays are identical but their dimensionalities differ, the primary determinant of the sorting order is the dimensionality.

=

Description: Determines whether two arrays are equal.

Example:

1
2
3
4
5
 
postgres=#SELECT ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] AS RESULT;
 result 
--------
 t
(1 row)

<>

Description: Determines whether two arrays are not equal.

Example:

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

<

Description: Determines whether one array is less than another array.

Example:

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

>

Description: Determines whether one array is greater than another array.

Example:

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

<=

Description: Determines whether one array is less than or equal to another array.

Example:

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

>=

Description: Determines whether one array is greater than or equal to another array.

Example:

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

@>

Description: Determines whether one array contains another array.

Example:

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

<@

Description: Determines whether one array is contained within another array.

Example:

1
2
3
4
5
 
postgres=#SELECT ARRAY[2,7] <@ ARRAY[1,7,4,2,6] AS RESULT;
 result 
--------
 t
(1 row)

&&

Description: Determines whether one array overlaps with another array (shares common elements).

Example:

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

||

  • Description: Concatenates arrays.

    Example:

    1
2
3
4
5
    		 
    postgres=#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
    		 
    postgres=#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: Concatenates elements with an array.

    Example:

    1
2
3
4
5
    		 
    postgres=#SELECT 3 || ARRAY[4,5,6] AS RESULT;
  result   
-----------
 {3,4,5,6}
(1 row)
  • Description: Concatenates arrays with elements.

    Example:

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