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.