PostgreSQL Array Functions and Operators

Introduction

Here the operators available for array types.

Array Operators

Array Functions

array_append() function

This function is used to append an element to the end of an array.

Syntax:

array_append (anyarray, anyelement)

Return Type: anyarray

Example

SELECT array_append(ARRAY[2,9], 6);

Here is the result.

 array_append
--------------
 {2,9,6}
(1 row)

array_cat() function

This function is used to concatenate two arrays.

Syntax:

array_cat (anyarray, anyarray)

Return Type: anyarray

Example

SELECT array_cat(ARRAY[1,2,3], ARRAY[4,5]);

Here is the result.

  array_cat
-------------
 {1,2,3,4,5}
(1 row)

array_ndims() function

This function is used to return the number of dimensions of the array.

Syntax:

array_ndims(anyarray)

Return Type: int

Example

SELECT array_ndims(ARRAY[[1,2,3], [4,5,6]]);

Here is the result.

 array_ndims
-------------
           2
(1 row)

array_dims() function

This function is used to return a text representation of array's dimensions.

Syntax:

array_dims (anyarray)

Return Type: text

Example

SELECT array_dims(ARRAY[[2,2,4], [5,3,6]]);

Here is the result.

 array_dims
------------
 [1:2][1:3]
(1 row)

array_fill() function

This function is used to return an array initialized with supplied value and dimensions, optionally with lower bounds other than 1.

Syntax:

array_fill(anyelement, int[], [, int[]])

Return Type: anyarray

Example

SELECT array_fill(7, ARRAY[3], ARRAY[2]);

Here is the result.

  array_fill
---------------
 [2:4]={7,7,7}
(1 row)

array_length() function

This function is used to return the length of the requested array dimension.

Syntax:

array_length(anyarray, int)

Return Type: int

Example

SELECT array_length(array[1,2,3], 1);

Here is the result.

 array_length
--------------
            3
(1 row)

array_lower() function

This function is used to return lower bound of the requested array dimension.

Syntax:

array_lower(anyarray, int)

Return Type: int

Example

SELECT array_lower('[0:2]={1,2,3}'::int[], 1);

Here is the result.

 array_lower
-------------
           0
(1 row)

array_prepend() function

This function is used to append an element to the beginning of an array.

Syntax:

array_prepend (anyelement, anyarray)

Return Type: anyarray

Example

SELECT array_prepend(1, ARRAY[2,3]);

Here is the result.

 array_prepend
---------------
 {1,2,3}
(1 row)

array_remove() function

This function is used to remove all elements equal to the given value from the array (array must be one-dimensional).

Syntax:

array_remove(anyarray, anyelement)

Return Type: anyarray

Example

SELECT array_remove(ARRAY[1,2,3,2], 2);

Here is the result.

 array_remove
---------------
  {1,3}
(1 row)

array_replace() function

This function is used to replace each array element equal to the given value with a new value.

Syntax:

array_replace(anyarray, anyelement, anyelement)

Return Type: anyarray

Example

SELECT array_replace(ARRAY[1,2,5,4], 5, 3);

Here is the result.

 array_replace
---------------
  {1,2,3,4}
(1 row)

array_to_string() function

This function is used to concatenate array elements using supplied delimiter and optional null string.

Syntax:

array_to_string(anyarray, text [, text])

Return Type: text

Example

SELECT array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*');

Here is the result.

 array_to_string
-----------------
 1,2,3,*,5
(1 row)

array_upper() function

This function is used to return upper bound of the requested array dimension.

Syntax:

array_upper(anyarray, int)

Return Type: int

Example

SELECT array_upper(ARRAY[1,8,3,7], 1);

Here is the result.

 array_upper
-------------
           4
(1 row)

string_to_array() function

This function is used to split string into array elements using supplied delimiter and optional null string.

Syntax:

string_to_array(text, text [, text])

Return Type: text[]

Example

SELECT string_to_array('xx~^~yy~^~zz', '~^~', 'yy');

Here is the result.

 string_to_array
-----------------
 {xx,NULL,zz}
(1 row)

unnest() function

This function is used to expand an array to a set of rows.

Syntax:

unnest(anyarray)

Return Type: setof anyelement

Example

SELECT unnest(ARRAY[1,2]);

Here is the result.

 unnest
--------
      1
      2
(2 rows)