In PostgreSQL we can use the array_replace() function to replace a specified element (or elements) in an array with another element.

The first argument is the array, the second is the element to replace, and the third is the element to replace it with.

Example

Here’s a simple example to demonstrate:

SELECT array_replace(ARRAY['Cat','Dog','Cat','Horse'], 'Cat', 'Bird');

Result:

{Bird,Dog,Bird,Horse}

Here I replaced Cat with Bird. There were two occurrences and so both occurrences were replaced.

No Match

If there are no matching elements nothing is replaced:

SELECT array_replace(ARRAY['Cat','Dog','Cat','Horse'], 'Bull', 'Bird');

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_replace(ARRAY['Cat','Dog','Cat','Horse'], 1, 'Bird');

Result:

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

Same with the third argument:

SELECT array_replace(ARRAY['Cat','Dog','Cat','Horse'], 'Bull', 1);

Result:

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

It is possible to overcome this error by using single quotes:

SELECT 
    array_replace(ARRAY['Cat','Dog','Cat','Horse'], '1', 'Bird'),
    array_replace(ARRAY['Cat','Dog','Cat','Horse'], 'Bull', '1');

Result:

    array_replace    |    array_replace    
---------------------+---------------------
 {Cat,Dog,Cat,Horse} | {Cat,Dog,Cat,Horse}

Or we could explicitly cast the integers as text:

SELECT 
    array_replace(ARRAY['Cat','Dog','Cat','Horse'], 1::text, 'Bird'),
    array_replace(ARRAY['Cat','Dog','Cat','Horse'], 'Bull', 1::text);

Result:

    array_replace    |    array_replace    
---------------------+---------------------
 {Cat,Dog,Cat,Horse} | {Cat,Dog,Cat,Horse}

Multi Dimensional Arrays

The array_repace() function works with multi dimensional arrays:

SELECT array_replace(ARRAY[ARRAY['Cat','Dog'],ARRAY['Cat','Horse']], 'Cat', 'Bird');

Result:

       array_replace       
---------------------------
 {{Bird,Dog},{Bird,Horse}}

Replacing NULL Values

We can use array_replace() to replace NULL values:

SELECT array_replace(ARRAY[null,'Dog',null,'Horse'], null, 'Bird');

Result:

{Bird,Dog,Bird,Horse}

We can also swap it around so that we replace a non-null value with null:

SELECT array_replace(ARRAY['Cat','Dog','Cat','Horse'], 'Cat', null);

Result:

{NULL,Dog,NULL,Horse}