英文:
psql - array_replace only replaces the first value
问题
以下是您要翻译的内容:
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}
英文:
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}
答案1
得分: 1
"array_replace()函数用第三个参数替换与第二个参数相等的每个数组元素。如果要替换多个旧值(第二个参数),您需要多次调用该函数。这就是为什么您的
UPDATE`语句不产生您期望的结果。
您需要对数组进行展开,从第二个表中获取所有展开元素的new_id
,并按group_id
对结果(新的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中进行测试。"
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论