如何使用复合类型元素为PostgreSQL表创建GENERATED列

首先纠正你创建表时的语法错误，PostgreSQL创建表需使用小括号而非大括号：

CREATE TABLE partner (
  sw objref ARRAY
);

要生成包含sw数组所有元素objid字段的数组列，你需要通过展开数组-提取字段-重新聚合的方式实现，PostgreSQL不支持sw[:].objid这类直接提取数组复合元素字段的简写语法。正确的ALTER TABLE语句如下：

ALTER TABLE partner ADD COLUMN sw_id int8[] GENERATED ALWAYS AS (
  array(SELECT elem.objid FROM unnest(sw) elem)
) STORED;

原理说明 #

• unnest(sw)：将sw数组展开为一行行的objref元素
• SELECT elem.objid：从每个展开的元素中提取objid字段
• array(...)：将提取出的objid值重新聚合为int8[]类型数组

测试验证 #

插入测试数据并查询，确认生成列生效：

-- 插入包含两个objref元素的数组
INSERT INTO partner (sw) VALUES (
  ARRAY[(1::nonnull_int8, 'server'::nonnull_string)::objref, (2::nonnull_int8, 'database'::nonnull_string)::objref]
);

-- 查询结果
SELECT sw, sw_id FROM partner;

查询结果中sw_id列会返回{1,2}，符合预期。

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