PostgreSQL offers a built-in ARRAY_REMOVE() function that is used to delete all the occurrences of a specific value. The ARRAY_REMOVE() function works on one-dimensional arrays. It can be used to remove any type of data, such as INT, TEXT, VARCHAR, etc.

This write-up will teach you how the ARRAY_REMOVE() function works in Postgres. So, let’s begin!

How to Use the ARRAY_REMOVE() Function in Postgres?

ARRAY_REMOVE() is an inbuilt array function that accepts an array and a specific number as arguments and deletes all the occurrences of that particular number from the input array. The input array must be one-dimensional:

ARRAY_REMOVE(input_array, num);

The input_array represents an array from which the input number “num” will be removed.

Example 1: How to Remove a Numeric Value From an Array in Postgres?

In this example, we will eliminate all the occurrences of “-1” from the given array:

SELECT ARRAY_REMOVE(
ARRAY[1, 23, 150, -1, 2, -3, -1, 12, 121], -1
);

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

From the output, you can observe that “-1” has been removed from the resultant array.

Example 2: How to Remove a String Value From an Array in Postgres?

This example will show you the usage of the ARRAY_REMOVE() function on the string-type data:

SELECT ARRAY_REMOVE(
ARRAY['Ambrose', 'Joseph', 'Alexa', 'Anna', 'Joseph', 'Stephanie', 'Joseph'], 'Joseph'
);

In this example program, a string array is passed as the first argument, and “Joseph” is passed as the second argument. Consequently, all the occurrences of “Joseph” will be eliminated from the given string array:

The output snippet verifies that the ARRAY_REMOVE() function successfully removes the specified string from the array.

Example 3: Is ARRAY_REMOVE() Case-sensitive?

Let’s run the following command to see if the ARRAY_REMOVE() function is case-sensitive or not:

SELECT ARRAY_REMOVE(
ARRAY['Ambrose', 'Joseph', 'Alexa', 'Joseph', 'Stephanie', 'Joseph'], 'joseph'
);

The output snippet proves that the ARRAY_REMOVE() function is case-sensitive.

Example 4: How to Use ARRAY_REMOVE() 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 remove “joe” from the st_name column. For that purpose, we will use the ARRAY_REMOVE() function as follows:

SELECT ARRAY_REMOVE(st_name, 'Joe')
FROM st_information;

The output snippet authenticates that a string “Joe” has been removed from the st_name column.

That’s it from this Postgres blog!

Conclusion

ARRAY_REMOVE() is an inbuilt array function that accepts an array and a specific number as arguments and deletes all the occurrences of that particular number from the input array. The input array must be one-dimensional. The ARRAY_REMOVE() function is case-sensitive. It can be used to remove/eliminate any type of data, such as INTEGER, TEXT, VARCHAR, etc. Postgres ARRAY_REMOVE() function is explained with practical examples in this write-up.