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 tbl(id int, country text, name text, VALUES int[]);
    
INSERT INTO tbl VALUES
  (1, 'USA', 'John Smith', '{1,2,3}')
, (2, 'USA', 'Jane Smith', '{0,1,3}')
, (3, 'USA', 'Jane Doe'  , '{1,1,1}')
, (4, 'USA', 'John Doe'  , '{0,2,4}')
, (5, 'USA', 'Frank Doe' , NULL)
;

CREATE AGGREGATE array_combine(anyarray) (
  SFUNC = array_cat
, STYPE = anyarray
-- , INITCOND  = '{}' --  to exclude NULL as result
);
5 rows affected

SELECT *, array_combine(values) OVER (ROWS BETWEEN UNBOUNDED PRECEDING
                                           AND UNBOUNDED FOLLOWING
                                           EXCLUDE CURRENT ROW) AS agg_values
FROM   tbl;


id
country
name
values
agg_values


1
USA
John Smith
{1,2,3}
{0,1,3,1,1,1,0,2,4}

2
USA
Jane Smith
{0,1,3}
{1,2,3,1,1,1,0,2,4}

3
USA
Jane Doe
{1,1,1}
{1,2,3,0,1,3,0,2,4}

4
USA
John Doe
{0,2,4}
{1,2,3,0,1,3,1,1,1}

5
USA
Frank Doe
null
{1,2,3,0,1,3,1,1,1,0,2,4}


-- duplicate names!
INSERT INTO tbl VALUES (6, 'USA', 'John Doe'  , '{0,2,4}');
1 rows affected

SELECT *, array_combine(values) OVER (ORDER BY name
                                      ROWS BETWEEN UNBOUNDED PRECEDING
                                           AND UNBOUNDED FOLLOWING
                                           EXCLUDE GROUP) AS agg_values
FROM   tbl;


id
country
name
values
agg_values


5
USA
Frank Doe
null
{1,1,1,0,1,3,0,2,4,0,2,4,1,2,3}

3
USA
Jane Doe
{1,1,1}
{0,1,3,0,2,4,0,2,4,1,2,3}

2
USA
Jane Smith
{0,1,3}
{1,1,1,0,2,4,0,2,4,1,2,3}

4
USA
John Doe
{0,2,4}
{1,1,1,0,1,3,1,2,3}

6
USA
John Doe
{0,2,4}
{1,1,1,0,1,3,1,2,3}

1
USA
John Smith
{1,2,3}
{1,1,1,0,1,3,0,2,4,0,2,4}

id	country	name	values	agg_values
1	USA	John Smith	{1,2,3}	{0,1,3,1,1,1,0,2,4}
2	USA	Jane Smith	{0,1,3}	{1,2,3,1,1,1,0,2,4}
3	USA	Jane Doe	{1,1,1}	{1,2,3,0,1,3,0,2,4}
4	USA	John Doe	{0,2,4}	{1,2,3,0,1,3,1,1,1}
5	USA	Frank Doe	null	{1,2,3,0,1,3,1,1,1,0,2,4}

id	country	name	values	agg_values
5	USA	Frank Doe	null	{1,1,1,0,1,3,0,2,4,0,2,4,1,2,3}
3	USA	Jane Doe	{1,1,1}	{0,1,3,0,2,4,0,2,4,1,2,3}
2	USA	Jane Smith	{0,1,3}	{1,1,1,0,2,4,0,2,4,1,2,3}
4	USA	John Doe	{0,2,4}	{1,1,1,0,1,3,1,2,3}
6	USA	John Doe	{0,2,4}	{1,1,1,0,1,3,1,2,3}
1	USA	John Smith	{1,2,3}	{1,1,1,0,1,3,0,2,4,0,2,4}