PostgreSQL中按条件更新复合类型数组元素的方法咨询

解决方案1：通用更新（支持匹配多个元素） #

使用unnest展开数组，通过CASE语句修改目标元素，再用array_agg重新聚合为数组，无需提前知晓元素索引：

UPDATE tbl
SET data_list = (
    SELECT array_agg(
        CASE WHEN elem.user_id = 11 
             THEN (elem.user_id, 'XXXX'::character(4))::mydata_t 
             ELSE elem 
        END
    )
    FROM unnest(tbl.data_list) AS elem
)
WHERE id = 2;

语句说明：

• unnest(tbl.data_list)：将目标行的复合类型数组拆分为单行记录
• CASE条件：判断每个元素的user_id是否等于目标值（示例中为11），是则替换value为'XXXX'，否则保留原元素
• array_agg：将处理后的单行元素重新组合为数组，赋值给data_list
• WHERE子句：限定仅更新id=2的行

解决方案2：单元素精准更新（依赖原元素完整值） #

如果确认数组中仅存在一个匹配user_id的元素，且知晓该元素的原value值，可通过array_position找到索引后直接更新：

UPDATE tbl
SET data_list[array_position(data_list, (11, 'YYYY')::mydata_t)] = (11, 'XXXX')::mydata_t
WHERE id = 2;

验证更新结果

执行更新后，可通过以下语句查看目标行内容：

SELECT * FROM tbl WHERE id = 2;

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