psql – array_replace 只替换第一个值

huangapple go评论67阅读模式
英文:

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.

huangapple
  • 本文由 发表于 2023年4月7日 01:17:40
  • 转载请务必保留本文链接:https://go.coder-hub.com/75952146.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定