Postgres 13

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 arrtbl (
   arrtbl_id serial PRIMARY KEY
 , arr int[]
);

INSERT INTO arrtbl (arr) VALUES 
  ('{{1,2,3},{2,15,32},{5,16,14}}')
, ('{{17,22},{1,15},{16,14}}')   -- dimensions can vary across rows!
, ('{}')
, (null);
4 rows affected

SELECT arrtbl_id, array_agg(a) AS a1
FROM   arrtbl t, unnest(t.arr[:][1]) a
GROUP  BY 1
ORDER  BY 1;


arrtbl_id
a1


1
{1,2,5}

2
{17,1,16}


SELECT arrtbl_id, array_agg(a.a ORDER BY a.ordinality)
FROM   arrtbl t
LEFT   JOIN LATERAL unnest(t.arr[:][1]) WITH ORDINALITY a ON true
GROUP  BY 1
ORDER  BY 1;


arrtbl_id
array_agg


1
{1,2,5}

2
{17,1,16}

3
{NULL}

4
{NULL}


-- Safer, faster
SELECT arrtbl_id, array_agg(elem)
FROM  (
   SELECT t.arrtbl_id, a.elem
   FROM   arrtbl t
   LEFT   JOIN LATERAL unnest(t.arr[:][1]) WITH ORDINALITY a(elem, ord) ON true
   ORDER  BY t.arrtbl_id, a.ord
   ) sub
GROUP  BY 1
ORDER  BY 1;


arrtbl_id
array_agg


1
{1,2,5}

2
{17,1,16}

3
{NULL}

4
{NULL}