Postgres 9.5

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

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create table tab (id int, col smallint[]);
insert into tab values
(1, array[5,3]),
(1, array[6,4]),
(2, array[3]),
(2, array[2,3]);

INSERT 0 4

SELECT * from tab

id
col

1
{5,3}

1
{6,4}

2
{3}

2
{2,3}

SELECT 4

SELECT id,MAX(array_length(col,1)) AS lat
          FROM tab GROUP BY id

id
lat

1
2

2
2

SELECT 2

SELECT 
array_agg(
          array_cat(
            col,
            array_fill(NULL::smallint, ARRAY[lat-COALESCE(array_length(col, 1),0)])
          )
       ) AS result
,tab.id
FROM tab
,LATERAL (SELECT id,MAX(array_length(col,1)) AS lat
          FROM tab GROUP BY id) s
GROUP BY tab.id

result
id

{{5,3},{5,3},{6,4},{6,4}}
1

{{3,NULL},{3,NULL},{2,3},{2,3}}
2

SELECT 2

SELECT 
s.id,
array_agg(
          array_cat(
            col,
            array_fill(NULL::smallint, ARRAY[lat-COALESCE(array_length(col, 1),0)])
          )
       ) AS result
       
FROM tab
,LATERAL (SELECT id,MAX(array_length(col,1)) AS lat
          FROM tab GROUP BY id) s
          GROUP BY s.id

id
result

1
{{5,3},{6,4},{3,NULL},{2,3}}

2
{{5,3},{6,4},{3,NULL},{2,3}}

SELECT 2

SELECT id, ARRAY_AGG(CASE WHEN ARRAY_LENGTH(col, 1) < max_length THEN (col || ARRAY_FILL(NULL::SMALLINT, ARRAY[max_length]))[1:max_length] ELSE col END) AS result
FROM tab, (SELECT MAX(ARRAY_LENGTH(col, 1)) AS max_length FROM tab) m
GROUP BY id

id
result

1
{{5,3},{6,4}}

2
{{3,NULL},{2,3}}

SELECT 2