Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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

insert into T values
    (1, array[null, 42]),   -- filled from the start
    (2, array[]::int[]);    -- we'll fill this later as we get values

update T set list[2] = 42 where id = 2;  -- we got a value for id 2 now

CREATE TABLE

INSERT 0 2

UPDATE 1

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

val
idx

null
1

42
2

SELECT 2

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

val
idx

42
1

SELECT 1

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

val
idx
ord

42
2
1

SELECT 1