如何更新PostgreSQL表中关联数组字段的对应值？

表结构定义 #

create table table_with_arrays
(
    dim_col_code_array integer[] -- 存储dict_table.array_code的外键数组
    dim_col_val_array  varchar[] -- 需要填充对应的dict_table.array_value数组
);

create table dict_table(
   array_code integer,
   array_value varchar
);

需求说明 #

table_with_arrays表的dim_col_code_array字段值为整数数组（如[10,300,400]），每个元素都是dict_table表array_code字段的外键。需要将每一行的dim_col_code_array元素按顺序关联dict_table，把对应的array_value按原顺序存入dim_col_val_array字段。

示例：

• 更新前table_with_arrays记录：[1,2,3], []
• dict_table记录：

  array_code	array_value
  1	FIRST
  2	SECOND
  3	THIRD

• 更新后table_with_arrays记录：[1,2,3], ['FIRST', 'SECOND', 'THIRD']

高效实现方案 #

方法1：使用unnest WITH ORDINALITY关联重组数组 #

这是PostgreSQL中处理数组顺序映射的高效方案，利用集合操作替代逐行循环，适合大数据量场景。

UPDATE table_with_arrays t
SET dim_col_val_array = agg_values
FROM (
    SELECT
        t2.ctid,
        array_agg(d.array_value ORDER BY u.ordinality) AS agg_values
    FROM table_with_arrays t2
    LEFT JOIN unnest(t2.dim_col_code_array) WITH ORDINALITY u(code, ordinality) ON true
    LEFT JOIN dict_table d ON d.array_code = u.code
    GROUP BY t2.ctid
) sub
WHERE t.ctid = sub.ctid;

关键逻辑说明：

• unnest(...) WITH ORDINALITY：将数组拆分为单行记录，同时生成ordinality字段标记元素在原数组中的位置，保证顺序不丢失。
• 关联dict_table匹配对应的array_value。
• array_agg(...) ORDER BY u.ordinality：按原始位置重新聚合数组，确保结果顺序与原数组完全一致。
• 用ctid作为行唯一标识（若表有主键，建议替换为主键字段，更规范），保证每一行能精准匹配聚合后的结果数组。

性能优化建议 #

• 给dict_table.array_code建立唯一索引：CREATE UNIQUE INDEX idx_dict_array_code ON dict_table(array_code);，大幅提升关联查询的速度。
• 若table_with_arrays数据量极大，可按主键范围分批执行UPDATE，避免长时间锁表影响业务。

内容的提问来源于stack exchange，提问作者Capacytron