PostgreSQL Array Functions

Introduction to PostgreSQL Array Functions

PostgreSQL array functions are basically 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 own array; for example, suppose data contains the integer value then we have integer[] array, if data contains the character value, then we have character[] array. PostgreSQL will automatically create an array of data even 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.

1. Array_append() function
2. Array_cat() function
3. Array_ndims() function
4. Array_dims() function
5. Array_fill() function
6. Array_length() function
7. Array_lower() function
8. Array_prepend() function
9. Array_remove() function
10. Array_replace() function
11. Array_to_string() function
12. Array_upper() function
13. String_to_array function
14. 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 is used to return the text dimension of 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 is used to return 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 is used to replace new elements, which were equal to give array 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 is used to concatenate 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 is used to split 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 is used to expand 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 was we have used with unset function)

Example

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

Output:

Recommended Articles

This is a guide to PostgreSQL Array Functions. Here we discuss the Functions of PostgreSQL Array along with the Syntax, Examples, and outputs. You may also have a look at the following articles to learn more –

1. PostgreSQL Vacuum
2. hstore in PostgreSQL
3. PostgreSQL CTE
4. PostgreSQL Variables