psql - array_replace only replaces the first value

Question

I'm using PSQL and trying to replace values in an array column using another table for the replacements, but it only replaces the first value in the array. Am I missing something in my command?

UPDATE table1 t1 
  SET ids = ARRAY_REPLACE(t1.ids, t2.old_id, t2.new_id::text) 
FROM table2 t2;

My tables:

Table 1 structure

    Column     |  Type  | Collation | Nullable | Default
---------------+--------+-----------+----------+---------
 group_id      | uuid   |           | not null |
 ids           | text[] |           | not null |

Table 1 Data (before)

group_id                             | ids
00000000-0000-4000-a000-00000000000a | {10002,10003,10000,10001}
00000000-0000-4000-a000-00000000000b | {20002,20003,20001,20000}

Table 2 Structure

   Column    |           Type           | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+---------
 old_id      | character varying        |           | not null |
 new_id      | uuid                     |           | not null |

Table 2 Data

old_id | new_id
10000  | 00000000-0000-4000-a000-000000000010
10001  | 00000000-0000-4000-a000-000000000011
10002  | 00000000-0000-4000-a000-000000000012
10003  | 00000000-0000-4000-a000-000000000013
20000  | 00000000-0000-4000-a000-000000000020
20001  | 00000000-0000-4000-a000-000000000021
20002  | 00000000-0000-4000-a000-000000000022
20003  | 00000000-0000-4000-a000-000000000023

Table 1 Data (after)

group_id                             | ids
00000000-0000-4000-a000-00000000000a | {10002,10003,00000000-0000-4000-a000-000000000010,10001}
00000000-0000-4000-a000-00000000000b | {20002,20003,20001,00000000-0000-4000-a000-000000000020}

Answer 1

The function array_replace() replaces each array element equal to the second argument with the third argument. If you want to replace more than one old value (the second argument) you have to call the function multiple times. That is why your UPDATE statement does not do what you expect.

You need to unnest the arrays, get new_id for all unnested elements from the second table, and aggregate the results (new ids) grouping them by group_id.

with select_new_ids as (
    select group_id, array_agg(new_id) as new_ids
    from table1 t1
    cross join unnest(ids) as u(old_id)
    join table2 t2 using(old_id)
    group by group_id
)
update table1 t set
    ids = new_ids
from select_new_ids n
where n.group_id = t.group_id;

Test it in db<>fiddle.