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?.
drop table if exists my_table;
create table my_table(p_no int, type text, name text, value int);
insert into my_table values
(1, 'A', 'Tomy', 1),
(1, 'A', 'Nick', 2),
(1, 'B', 'Tomy', 3),
(1, 'B', 'Nick', 4),
(1, 'C', 'Tomy', 5),
(1, 'C', 'Nick', 4),
(2, 'A', 'Tomy', 8),
(2, 'A', 'Nick', 7),
(2, 'B', 'Tomy', 5),
(2, 'B', 'Nick', 4);
DROP TABLE
CREATE TABLE
INSERT 0 10
select p_no, t[1] as "A", t[2] as "B", t[3] as "C"
from (
select p_no, array_agg(value order by type) as t
from my_table
where name = 'Tomy'
group by p_no
) s
| p_no | A | B | C |
|---|---|---|---|
| 1 | 1 | 3 | 5 |
| 2 | 8 | 5 | null |
SELECT 2