Updated May 31, 2023

## Introduction to PostgreSQL Array Functions

PostgreSQL array functions are used to concatenate the input values. In PostgreSQL, each type of data which was we have used in database tables, each type of data has its array; for example, suppose data contains the integer value, then we have an integer[] array; if data contains the character value, then we have character[] array. PostgreSQL will automatically create an array of data even if the user has defined its own data type. In PostgreSQL array_upper, array_lower, array_fill, unset, array_to_string, string_to_array, array_append, array_cat array function are available.

### PostgreSQL Array Functions

Below is the array function which was available in PostgreSQL.

- Array_append() function
- Array_cat() function
- Array_ndims() function
- Array_dims() function
- Array_fill() function
- Array_length() function
- Array_lower() function
- Array_prepend() function
- Array_remove() function
- Array_replace() function
- Array_to_string() function
- Array_upper() function
- String_to_array function
- Unset function

Below is the description of the array function in PostgreSQL.

#### 1. Array_append() function

This array function is used to append the element with the end of the array. Below is the example and syntax of the array_append function.

**Syntax **

Array_append(array ((Any_array)Any array element which was we have used with array_append function, element(Any element which was used with array_append function))

**Example **

`SELECT array_append(array[1, 2, 3, 4, 5], 6);`

**Output:**

#### 2. Array_cat() function

This array function is used to concatenate two arrays in PostgreSQL. Below is the example and syntax of the array_cat function.

**Syntax **

Array_cat(Array1 (Any array element which was we have used with array_cat function), Array2 (Any array element which was we have used with array_cat function))

**Example **

`SELECT array_cat(ARRAY[11, 12, 13, 14, 15], ARRAY[16, 17, 18, 19,20]);`

**Output:**

#### 3. Array_ndims() function

This array function is used to return the dimension of the array. Below is the example and syntax of the array_ndims function.

**Syntax **

Array_ndims(Any_array (Any array element which was we have used with array_ndims function), any_array)

**Example **

`SELECT array_ndims(ARRAY[[11, 12, 13, 14, 15], [16, 17, 18, 19,20]]);`

**Output:**

#### 4. Array_dims() function

This array function returns the text dimension of the array representation. Below is the example and syntax of the array_dims function.

**Syntax **

Array_dims(Any_array (Any array element which was we have used with Array_dims function), any_array)

**Example **

`SELECT array_dims(ARRAY[[11, 12, 13, 14, 15], [16, 17, 18, 19,20]]);`

**Output:**

#### 5. Array_fill() function

This array function returns the array initialized dimension and supplied value. Below is the example and syntax of the array_fill function.

**Syntax **

Array_fill(Any_element (array element which was used with array_fill function ), int[], int[])

**Example**

`SELECT array_fill(20, ARRAY[15], ARRAY[10]);`

**Output:**

#### 6. Array_length() function

This array function is used to return the length of an array. Below is the example and syntax of the array_length function.

**Syntax **

Array_length(Any_array (Any array element which was we have used with Array_length function), integer_number)

**Example **

`SELECT array_length(array[11, 12, 13, 14, 15], 1);`

**Output:**

#### 7. Array_lower() function

This array function is used to return the lower bound of an array. Below is the example and syntax of the array_lower function.

**Syntax **

Array_lower(Any_array (Any array element which was we have used with array_lower function), integer_number)

**Example **

`SELECT array_lower('[0:4]={11, 12, 13, 14, 15}'::int[], 1);`

**Output:**

#### 8. Array_prepend() function

This array function is used to append the element beginning of the array. Below is the example and syntax of the array_prepend function.

**Syntax **

Array_prepend(array (element(Any element which was used with array_prepend function), (Any_array)Any array element which was we have used with array_prepend function))

**Example**

`SELECT array_prepend(6, array[1, 2, 3, 4, 5]);`

**Output:**

#### 9. Array_remove() function

This array function is used to remove elements that were equal to the given array. Below is the example and syntax of the array_remove function.

**Syntax **

Array_remove(array ((Any_array)Any array element which was we have used with array_remove function, element(Any element which was used with array_remove function))

**Example**

`SELECT array_remove(ARRAY[11, 12, 13, 14, 15, 11, 11], 11);`

**Output:**

#### 10. Array_replace() function

This array function replaces new elements, equal to giving an array of old elements. Below is the example and syntax of the array_replace function.

**Syntax **

Array_replace(array ((Any_array)Any array element which was we have used with array_replace function, element(Any element which was used with array_replace function), element(Any element which was used with array_replace function))

**Example **

`SELECT array_replace(ARRAY[11, 12, 13, 14, 15, 11, 11], 11, 21);`

**Output:**

#### 11. Array_to_string() function

This array function concatenates array elements using null sting and supplied parameters. Below is the example and syntax of the array_to_string function.

**Syntax **

Array_to_string(Any_array (Any array element which was we have used with array_to_string function), text1[], [text)

**Example **

`SELECT array_to_string(ARRAY[11, 12, 13, NULL, 15], ',', '*');`

**Output:**

#### 12. Array_upper() function

This array function is used to return the upper bound of an array. Below is the example and syntax of the array_upper function.

**Syntax**

Array_upper(Any_array (Any array element which was we have used with Array_upper function), integer_number)

**Example**

`SELECT array_upper(ARRAY[11, 12, 13, 14, 15], 1);`

**Output:**

#### 13. String_to_array() function

This array function splits the string into array elements. Below is the example and syntax of the string_to_array function.

**Syntax **

string_to_array(text(any text used with string_to_array function), text[], [text)

**Example **

`SELECT string_to_array('AB~^~CD~^~EF', '~^~', 'XY');`

**Output:**

#### 14. Unset() function

This array function expands the array into a set of rows. Below is the example and syntax of the unset function.

**Syntax **

Unset(Any_array (Any array element which we have used with unset function)

**Example**

`SELECT unnest(ARRAY[11, 12, 13, 14, 15]);`

**Output:**

### Recommended Articles

We hope that this EDUCBA information on “PostgreSQL Array Functions” was beneficial to you. You can view EDUCBA’s recommended articles for more information.