EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Array Functions
 

PostgreSQL Array Functions

Updated May 31, 2023

PostgreSQL Array Functions

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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:

PostgreSQL Array Functions output 1

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:

PostgreSQL Array Functions output 2

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:

PostgreSQL Array Functions output 3

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:

PostgreSQL Array Functions output 4

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:

PostgreSQL Array Functions output 5

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:

output 6

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:

output 7

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:

output 8

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:

output 9

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:

output 10

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:

output 11

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:

output 12

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:

output 13

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:

output 14

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.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW