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
, extra_info varchar(3)
, month text
, date date
, value int
);
INSERT INTO tbl VALUES
(1, 'abc', 'jan', '2012-01-01', 10)
, (1, 'abc', 'feb', '2012-02-01', 20)
, (2, 'def', 'jan', '2012-01-01', 10)
, (2, 'def', 'feb', '2012-02-01', 5)
, (1, 'abc', 'jan', '2012-01-01', 15)
, (3, 'ghi', 'mar', '2012-03-01', 15)
;
CREATE TABLE
INSERT 0 6
SELECT * FROM crosstab(
$$
SELECT id, min(extra_info), month, sum(value) AS value
FROM tbl
GROUP BY id, month
ORDER BY id, month
$$
, $$
VALUES
('jan'::text), ('feb'), ('mar'), ('apr'), ('may'), ('jun')
, ('jul'), ('aug'), ('sep'), ('oct'), ('nov'), ('dec')
$$
) AS ct (id int, extra text
, jan int, feb int, mar int, apr int, may int, jun int
, jul int, aug int, sep int, oct int, nov int, dec int);
| id | extra | jan | feb | mar | apr | may | jun | jul | aug | sep | oct | nov | dec |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | abc | 25 | 20 | null | null | null | null | null | null | null | null | null | null |
| 2 | def | 10 | 5 | null | null | null | null | null | null | null | null | null | null |
| 3 | ghi | null | null | 15 | null | null | null | null | null | null | null | null | null |
SELECT 3
-- 0 instead of NULL
SELECT id, extra
, COALESCE(jan, 0) AS jan
, COALESCE(feb, 0) AS feb
, COALESCE(mar, 0) AS mar
, COALESCE(apr, 0) AS apr
, COALESCE(may, 0) AS may
, COALESCE(jun, 0) AS jun
, COALESCE(jul, 0) AS jul
, COALESCE(aug, 0) AS aug
, COALESCE(sep, 0) AS sep
, COALESCE(oct, 0) AS oct
, COALESCE(nov, 0) AS nov
, COALESCE(dec, 0) AS dec
FROM crosstab(
$$
SELECT id, min(extra_info), month, sum(value) AS value
FROM tbl
GROUP BY id, month
ORDER BY id, month
$$
, $$
VALUES
('jan'::text), ('feb'), ('mar'), ('apr'), ('may'), ('jun')
, ('jul'), ('aug'), ('sep'), ('oct'), ('nov'), ('dec')
$$
) AS ct (id int, extra text
, jan int, feb int, mar int, apr int, may int, jun int
, jul int, aug int, sep int, oct int, nov int, dec int);
| id | extra | jan | feb | mar | apr | may | jun | jul | aug | sep | oct | nov | dec |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | abc | 25 | 20 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2 | def | 10 | 5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 3 | ghi | 0 | 0 | 15 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
SELECT 3
SELECT id, min(extra_info) AS extra
, sum(value) FILTER (WHERE month = 'jan') AS jan
, sum(value) FILTER (WHERE month = 'feb') AS feb
, sum(value) FILTER (WHERE month = 'mar') AS mar
, sum(value) FILTER (WHERE month = 'apr') AS apr
, sum(value) FILTER (WHERE month = 'may') AS may
, sum(value) FILTER (WHERE month = 'jun') AS jun
, sum(value) FILTER (WHERE month = 'jul') AS jul
, sum(value) FILTER (WHERE month = 'aug') AS aug
, sum(value) FILTER (WHERE month = 'sep') AS sep
, sum(value) FILTER (WHERE month = 'oct') AS oct
, sum(value) FILTER (WHERE month = 'nov') AS nov
, sum(value) FILTER (WHERE month = 'dec') AS dec
FROM tbl
GROUP BY id
ORDER BY id;
| id | extra | jan | feb | mar | apr | may | jun | jul | aug | sep | oct | nov | dec |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | abc | 25 | 20 | null | null | null | null | null | null | null | null | null | null |
| 2 | def | 10 | 5 | null | null | null | null | null | null | null | null | null | null |
| 3 | ghi | null | null | 15 | null | null | null | null | null | null | null | null | null |
SELECT 3