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 mytable
    ("year" int, "month" int, "week" int, "type" varchar(1), "count" int)
;
    
INSERT INTO mytable
    ("year", "month", "week", "type", "count")
VALUES
    (2021, 1, 1, 'A', 5),
    (2021, 1, 1, 'B', 6),
    (2021, 1, 1, 'C', 7),
    (2021, 1, 2, 'A', 0),
    (2021, 1, 2, 'B', 8),
    (2021, 1, 2, 'C', 9)
;

SELECT * FROM mytable;

CREATE TABLE



INSERT 0 6




year
month
week
type
count


2021
1
1
A
5

2021
1
1
B
6

2021
1
1
C
7

2021
1
2
A
0

2021
1
2
B
8

2021
1
2
C
9



SELECT 6


SELECT
    year, month, week,
    MAX("count") FILTER (WHERE type = 'A') as A,
    MAX("count") FILTER (WHERE type = 'B') as B,
    MAX("count") FILTER (WHERE type = 'C') as C
FROM mytable
GROUP BY year, month, week
ORDER BY year, month, week


year
month
week
a
b
c


2021
1
1
5
6
7

2021
1
2
0
8
9



SELECT 2


SELECT
    year, month, week,
    MAX (CASE WHEN type = 'A' THEN "count" END) AS A,
    MAX (CASE WHEN type = 'B' THEN "count" END) AS B,
    MAX (CASE WHEN type = 'C' THEN "count" END) AS C
FROM mytable
GROUP BY year, month, week
ORDER BY year, month, week


year
month
week
a
b
c


2021
1
1
5
6
7

2021
1
2
0
8
9



SELECT 2