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?.

select version();

version

PostgreSQL 13.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit

SELECT 1

create table table1 (
  nummer int,
  name text,
  result numeric[]
);
insert into table1 values (1, 'Kevin', null);

create table table2 (
  nummer int,
  exam numeric,
  profnr numeric
);
insert into table2 values (1, 2.0, 10), (1, 3.0, 20);

CREATE TABLE

INSERT 0 1

CREATE TABLE

INSERT 0 2

with buildarr as (
  select nummer, array_agg(array[exam, profnr]) result
    from table2
   group by nummer
)
update table1
   set result = buildarr.result
  from buildarr
 where buildarr.nummer = table1.nummer;

UPDATE 1

select * from table1;

nummer
name
result

1
Kevin
{{2.0,10},{3.0,20}}

SELECT 1