We can use the array_remove() function in PostgreSQL to remove elements from an array.

We pass two arguments when calling the function; the first is the array and the second argument is the element to remove. All occurrences of the specified element are removed.

The function returns an identical array, but without the specified element/s.

Example

Here’s an example to demonstrate:

SELECT array_remove(array['Cat','Dog','Cat','Horse'], 'Cat');

Result:

{Dog,Horse}

In this case I removed Cat from the array. This value occurred twice in the source array, and so both elements were removed.

No Match

If there are no matching elements nothing is removed:

SELECT array_remove(array['Cat','Dog','Cat','Horse'], 'Ant');

Result:

{Cat,Dog,Cat,Horse}

In this case an identical array was returned.

Wrong Types

Here’s what happens when the second argument’s type doesn’t match the array type:

SELECT array_remove(array['Cat','Dog','Cat','Horse'], 1);

Result:

ERROR:  function array_remove(text[], integer) does not exist
LINE 1: SELECT array_remove(array['Cat','Dog','Cat','Horse'], 1);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

And:

SELECT array_remove(array[1,2,3], 'Ant');

Result:

ERROR:  invalid input syntax for type integer: "Ant"
LINE 1: SELECT array_remove(array[1,2,3], 'Ant');
                                          ^

Regarding the first example, I can stop this error by using single quotes for the second argument:

SELECT array_remove(array['Cat','Dog','Cat','Horse'], '1');

Result:

{Cat,Dog,Cat,Horse}

Or I could explicitly cast the integer as text:

SELECT array_remove(array['Cat','Dog','Cat','Horse'], 1::text);

Result:

{Cat,Dog,Cat,Horse}

Multi Dimensional Arrays

As of this writing (PostgreSQL 16) the array_remove() function only works with one dimensional arrays. Here’s what happens if I try to use it on a multi dimensional array:

SELECT array_remove(array[array['Cat','Dog'],array['Cat','Horse']], 'Cat');

Result:

ERROR:  removing elements from multidimensional arrays is not supported

Removing NULL Values

We can use array_remove() to remove NULL values:

SELECT array_remove(array[1,null,3,null,5,null,7], null);

Result:

{1,3,5,7}