Update multiple values from an array with array_replace()

Question

I am trying to do an update of a table in PostgreSQL.

In fact, I am trying to update an array. I have an table call switch_ids of OLD_ID, NEW_ID and I have the table TABLE_TO_UPDATE with a column my_array (an array of ids). I want to modify some of the ids in this array. I do something like that:

UPDATE TABLE_TO_UPDATE
SET my_array=array_replace(my_array,OLD_ID,NEW_ID)
FROM switch_ids
WHERE switch_ids.old_id = ANY(my_array);

The problem is that when there are multiple values to change in the same row (in my_array), it only changes one value and not all. How can I update them all at the same time? Nested calls?

Answer 1

There must be some kind of misunderstanding. array_replace() (pg 9.3+) replaces all occurrances of the item, not just the first. Consider:

SELECT array_replace(ARRAY[5,1,2,5,4,5], 5, 3);

Result:

{3,1,2,3,4,3}

SQL Fiddle.

Per documentation:

replace each array element equal to the given value with a new value