# ARRAY_REPLACE() Function in PostgreSQL

> The ARRAY_REPLACE() is an inbuilt array function in Postgres that allows us to replace all the occurrences of an array element with a new element.

PostgreSQL provides several built-in functions that perform different functionalities on the arrays. For instance, the **ARRAY_LENGTH()** function is used to find the length of an array, the **ARRAY_CAT()** is used to concatenate two arrays, etc. The **ARRAY_REPLACE()** is also an array function that replaces an array's specific value with a new value.

This write-up will teach you how to replace a specific value in an array using the Postgres **ARRAY_REPLACE()** function. So, let’s start!

 **How to Use ARRAY_REPLACE() Function in PostgreSQL?**

The ARRAY_REPLACE() is an inbuilt array function in Postgres that allows us to replace all the occurrences of an array element with a new element. It accepts three arguments: an array, an element to be replaced, and an element that will replace the targeted array element:
    
    
    ARRAY_REPLACE(arr, val_1, val_2);

In the above snippet:

\- arr represents an array to be modified.  
\- val_1 represents the array element to be replaced.  
\- val_2 represents a new element that will be inserted in the targeted array in place of val_1.

 **Example 1: How to Replace an Integer Value in an Array Using ARRAY_REPLACE() Function?**

The below-given code will replace all the occurrences of the “-1” with 5 in the given array:
    
    
    SELECT ARRAY_REPLACE(
    ARRAY[1, 23, 150, -1, 2, -3, -1, 12, 121], -1, 5
    );

In the above example program, an array is passed as the first argument, “-1” is passed as the second argument, and 5 is passed as the third argument. Consequently, all the occurrences of “-1” will be replaced with 5 in the input array:

The output shows that “-1” has been replaced with “5” in the resultant array.

 **Example 2: How to Replace a String Value in an Array Using ARRAY_REPLACE() Function?**

Let’s learn how to use the ARRAY_REPLACE() function on the string-type array in Postgres:
    
    
    SELECT ARRAY_REPLACE(
    ARRAY['Ambrose', 'Joe', 'Alexa', 'Joe', 'Mike', 'Joe'], 'Joe','Joseph'
    );

In the above snippet, the ARRAY_REPLACE() function is used to replace “Joe” with “Joseph”:

The output proves that the targeted element has been replaced with “Joseph” in the resultant array.

 **Example 3: Is ARRAY_REPLACE() Case-sensitive?**

Let’s run the following code to see if the ARRAY_REPLACE() function is case-sensitive or not:
    
    
    SELECT ARRAY_REPLACE(
    ARRAY['Ambrose', 'Joe', 'Alexa', 'Joe', 'Mike', 'Joe'], 'joe','Joseph'
    );

The output shows that the ARRAY_REPLACE() function didn’t replace the targeted element. It proves that the ARRAY_REPLACE() function is case-sensitive.

 **Example 4: How to Use ARRAY_REPLACE() Function on Table’s Data?**

We have created a table named “st_information” that contains the following data:
    
    
    SELECT * FROM st_information;

Suppose we want to replace “Joe” with “Alex” in the st_name column. For that purpose, we will use the ARRAY_REPLACE() function as follows:
    
    
    SELECT ARRAY_REPLACE(st_name, 'Joe', 'Alex')
    FROM st_information;

The above snippet proves that an array element “Joe” has been replaced with “Alex”.

 **Conclusion**

The ARRAY_REPLACE() is an inbuilt array function in Postgres that allows us to replace all the occurrences of an array element with a new element. It accepts three arguments: an array, an element to be replaced, and an element that will replace the targeted array element. Postgres ARRAY_REPLACE() function is explained with practical examples in this write-up.

---
[View this page online](https://www.commandprompt.com/education/array_replace-function-in-postgresql/)