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 subject (id int, name text);
INSERT INTO subject VALUES
(1, 'fruit')
, (2, 'drink')
, (3, 'vege')
, (4, 'fish')
;
CREATE TABLE journal (id int, subj int, reference text, value int);
INSERT INTO journal VALUES
(1, 1, 'foo', 30)
, (2, 2, 'bar', 20)
, (3, 1, 'bar', 35)
, (4, 1, 'bar', 10)
, (5, 2, 'baz', 25)
, (6, 4, 'foo', 30)
, (7, 4, 'bar', 40)
, (8, 1, 'baz', 20)
, (9, 2, 'bar', 5)
;
4 rows affected
9 rows affected
SELECT *
FROM crosstab(
'SELECT reference, subj, sum(value)
FROM journal
GROUP BY 1, 2
ORDER BY 1, 2'
, $$VALUES (1), (2), (3), (4)$$
) AS ct (reference text, fruit int, drink int, vege int, fish int);
| reference | fruit | drink | vege | fish |
|---|---|---|---|---|
| bar | 45 | 25 | null | 40 |
| baz | 20 | 25 | null | null |
| foo | 30 | null | null | 30 |