如何避免PostgreSQL中unnest()跳过未设置的数组元素？

问题场景 #

原本期望unnest()返回数组从索引1到array_length()对应的每一行，但实际行为不符。以下是示例：

测试代码 #

create table T (
    id int,
    list int[]
);

insert into T values
    (1, array[null, 42]),   -- 初始填充
    (2, array[]::int[]);    -- 后续获取值时再填充

update T set list[2] = 42 where id = 2;  -- 现在获取到id=2的值

select x.* from T, unnest(T.list) with ordinality as x (val, idx) where id = 1;
select x.* from T, unnest(T.list) with ordinality as x (val, idx) where id = 2;

查询输出 #

val | idx 
-----+-----
     |   1
  42 |   2

 val | idx 
-----+-----
  42 |   1

第二个查询中42的索引显示为1，破坏了依赖元素位置的业务逻辑。虽然认可PostgreSQL稀疏数组不盲目填充NULL的设计（节省存储空间，第二个数组存储为[2:2]={42}而非{NULL,42}），但当前场景需要保留原始索引。

背景 #

需存储数十亿个float8值，数据以EAV（实体-属性-值）格式传入，按实体聚合为数组，属性作为数组索引。该设计虽不符合关系型规范，但数据量过大，无法用其他方式管理。

最佳解决方案 #

利用数组的lower()和upper()函数获取数组的实际上下界，结合generate_series()生成完整的索引序列，再通过左连接关联unnest()的结果，即可保留所有原始索引位置，缺失的位置显示为NULL，同时不改变原数组的稀疏存储方式。

通用查询示例 #

SELECT
    T.id,
    gs.idx AS 原始索引,
    x.val AS 值
FROM
    T,
    generate_series(lower(T.list), upper(T.list)) AS gs(idx)
LEFT JOIN
    unnest(T.list) WITH ORDINALITY AS x(val, 临时序号)
ON
    gs.idx = lower(T.list) + x.临时序号 - 1
WHERE
    T.id IN (1, 2)
ORDER BY
    T.id, gs.idx;

查询结果 #

id | 原始索引 | 值 
----+----------+-----
  1 |        1 |    
  1 |        2 |  42
  2 |        2 |  42

原理说明 #

1. lower(T.list)获取数组的起始索引，upper(T.list)获取数组的结束索引；
2. generate_series()生成从起始到结束的所有索引值，确保每个位置都被覆盖；
3. unnest(...) WITH ORDINALITY得到的序号是从1开始的连续值，通过lower(T.list) + 临时序号 - 1转换为数组的原始索引，再与生成的索引序列左连接，即可匹配到对应的值，缺失的位置自然显示为NULL。

该方案兼顾了存储空间的节省和业务对索引位置的需求，且针对大数据量场景性能表现优异。

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