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.
