# Understanding Array Functions

> PostgreSQL supports various built-in array functions, such as ARRAY_PREPEND(), ARRAY_CAT(), etc., to deal with the array's data efficiently.

PostgreSQL is a commonly used relational database that supports the **ARRAY** data type to store the data of the same type in consecutive blocks of memory. To deal with the array's data efficiently, Postgres supports a variety of built-in functions, such as ARRAY_PREPEND(), ARRAY_CAT(), etc. Each of these array functions has distinct objectives/functionality.

This post will discuss the below-stated array functions in PostgreSQL:

\- ARRAY_APPEND()  
\- ARRAY_PREPEND()  
\- ARRAY_CAT()  
\- ARRAY_REPLACE( )  
\- ARRAY_REMOVE( )

 **PostgreSQL ARRAY_APPEND() Function**

ARRAY_APPEND() is one of the most frequently used array functions that append a new element at the end of an array using the following syntax:
    
    
    ARRAY_APPEND(array, new_element);

Here, "new_element" represents the element you want to add, and "array" represents the array where you want to append the new element.

 **Example: Applying ARRAY_APPEND**

The following example uses the “ARRAY_APPEND()” function to insert “.com” element at the end of the array:
    
    
    SELECT ARRAY_APPEND(ARRAY['Welcome', 'to', 'commandprompt'], '.com');

The output illustrates that the desired element has been successfully appended/inserted at the end of the provided array:

Follow our dedicated guide on how to use [ARRAY_APPEND()](<https://www.commandprompt.com/education/postgresql-array_append-function-with-examples/>) function to learn more about it.

 **PostgreSQL ARRAY_PREPEND() Function**

The **ARRAY_PREPEND()** function works in a contrary manner to the ARRAY_APPEND() function. It adds/inserts a new element at the beginning of an array by utilizing the below-provided syntax:
    
    
    ARRAY_PREPEND(new_element, array);

Here, "new_element" represents the element you want to add, and "array" represents the array where you want to prepend the new element.

 **Example: Applying ARRAY_PREPEND**

The following example uses the “ARRAY_PREPEND()” function to append “Hello” at the start of the array:
    
    
    SELECT ARRAY_PREPEND('Hello', ARRAY['Welcome', 'to', 'commandprompt']);

The output displays that the provided element has been inserted at the start of the given array:

 **PostgreSQL ARRAY_CAT() Function**

As the name itself describes, the “ARRAY_CAT()” function concatenates/combines given arrays and retrieves a single array:
    
    
    SELECT ARRAY_CAT(array_1, array_2);

Here, the “array_1” and “array_2” are the arrays to be combined.

 **Example: Applying ARRAY_CAT()**

The following example code concatenates two TEXT-type arrays:
    
    
    SELECT ARRAY_CAT(ARRAY['Hello', '!!!'], ARRAY['Welcome', 'to', 'commandprompt']);

The given arrays have been successfully concatenated:

For more details, check out the following guide on concatenating multiple arrays using [ARRAY_CAT()](<https://www.commandprompt.com/education/array_cat-function-in-postgresql/>) function.

 **PostgreSQL ARRAY_REPLACE() Function**

PostgreSQL allows us to replace an element in an array by specifying the “new element” and the “element to be replaced” in the **ARRAY_REPLACE** function. Here is a simple syntax that will allow you to comprehend this function better:
    
    
    ARRAY_REPLACE(array, existing_element, new_element);

The “new_element” will replace all the occurrences of the “existing_element”.

 **Example: Applying ARRAY_REPLACE() Function**

In the provided example code, the “Hello” element will be replaced with the “Hi”:
    
    
    SELECT ARRAY_REPLACE(ARRAY['Hello', 'Welcome', 'to', 'commandprompt'], 'Hello', 'Hi!');

The selected element has been successfully replaced with the desired element:

For more details, check our detailed guide on [ARRAY_REPLACE()](<https://www.commandprompt.com/education/array_replace-function-in-postgresql/>) Function in PostgreSQL.

 **PostgreSQL ARRAY_REMOVE() Function**

Use the ARRAY_REMOVE() function to remove/delete all the occurrences of any unwanted elements from a particular array:
    
    
    ARRAY_REMOVE(array, element);

Here, the element represents any array element that needs to be deleted/removed.

 **Example: Applying the ARRAY_REMOVE Function**

The following example utilizes the ARRAY_REMOVE() function to remove an element “Hello” from the given array:
    
    
    SELECT ARRAY_REMOVE(ARRAY['Hello', 'Welcome', 'to', 'commandprompt', 'Hello'], 'Hello');

The ARRAY_REMOVE() function has successfully removed all the occurrences of the “Hello” element from the provided array:

Check out the following guide to learn various use cases of the [ARRAY_REMOVE()](<https://www.commandprompt.com/education/array_remove-function-in-postgresql/>) function in PostgreSQL.

 **Conclusion**

PostgreSQL supports various built-in array functions, such as **ARRAY_PREPEND()** , **ARRAY_CAT()** , etc., to deal with the array's data efficiently. Each of these array functions offers distinct objectives/functionality. This guide has elaborated on some of the most frequently used array functions along with their basic syntax and suitable examples.

---
[View this page online](https://www.commandprompt.com/education/understanding-array-functions/)