parray_replace只替换第一个值

Question

我正在使用PSQL并尝试使用另一个表替换数组列中的值，但它只替换数组中的第一个值。我的命令有什么遗漏吗?

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

我的表:表1结构

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

表1数据(前)

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

表2结构

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

表2数据

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

表1数据(后)

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

函数array_replace()用第三个参数替换等于第二个参数的每个数组元素。如果要替换多个旧值(第二个参数)，则必须多次调用该函数。这就是为什么你的UPDATE语句没有做你所期望的。

您需要解开数组，从第二个表中获得所有未嵌套元素的new_id，并将结果(新id)按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;

在db<>fiddle中测试。