Postgres 15

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 data (id int, ujvj float8[]);
INSERT INTO DATA VALUES
  (1, '{{5.3, 0.05}, {3.5, 0.6}, {1.2,0.01}}')
, (2, '{{1.2,0.01,6}, {5.3, 0.05,6}, {3.5, 0.6,6}}')
, (3, '{}')
, (4, null)
;

CREATE TABLE



INSERT 0 4


SELECT d.id, a.*
FROM   data d
CROSS  JOIN LATERAL (
   SELECT array_agg(d.ujvj[d1][1]) AS uj
        , array_agg(d.ujvj[d1][2]) AS vj
   FROM   generate_subscripts(d.ujvj, 1) d1
   ) a;


id
uj
vj


1
{5.3,3.5,1.2}
{0.05,0.6,0.01}

2
{1.2,5.3,3.5}
{0.01,0.05,0.6}

3
null
null

4
null
null



SELECT 4


-- preserve empty array:
SELECT d.id
     , COALESCE(a.uj, d.ujvj) AS uj
     , COALESCE(a.vj, d.ujvj) AS vj
FROM   data d
CROSS  JOIN LATERAL (
   SELECT array_agg(d.ujvj[d1][1]) AS uj
        , array_agg(d.ujvj[d1][2]) AS vj
   FROM   generate_subscripts(d.ujvj, 1) d1
   ) a;


id
uj
vj


1
{5.3,3.5,1.2}
{0.05,0.6,0.01}

2
{1.2,5.3,3.5}
{0.01,0.05,0.6}

3
{}
{}

4
null
null



SELECT 4

id	uj	vj
1	{5.3,3.5,1.2}	{0.05,0.6,0.01}
2	{1.2,5.3,3.5}	{0.01,0.05,0.6}
3	null	null
4	null	null