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 subscriber (create_timestamp timestamp, subscriber_id int);
insert into subscriber values ('2021-01-01', 3), ('2021-02-01', 12), ('2021-04-01', 4)
, ('2021-04-11', 5);
4 rows affected
SELECT t.cdate
, COALESCE(p.total_count, 0) total_count
FROM (select generate_series('2021-01-01'::timestamp, '2021-12-15', '1 month') as cdate) t
LEFT JOIN (SELECT DATE_TRUNC('month',create_timestamp) create_timestamp
, SUM(subscriber_id) total_count
FROM subscriber
GROUP BY DATE_TRUNC('month',create_timestamp)) p
ON t.cdate = p.create_timestamp
| cdate | total_count |
|---|---|
| 2021-01-01 00:00:00 | 3 |
| 2021-02-01 00:00:00 | 12 |
| 2021-03-01 00:00:00 | 0 |
| 2021-04-01 00:00:00 | 9 |
| 2021-05-01 00:00:00 | 0 |
| 2021-06-01 00:00:00 | 0 |
| 2021-07-01 00:00:00 | 0 |
| 2021-08-01 00:00:00 | 0 |
| 2021-09-01 00:00:00 | 0 |
| 2021-10-01 00:00:00 | 0 |
| 2021-11-01 00:00:00 | 0 |
| 2021-12-01 00:00:00 | 0 |
SELECT DATE_TRUNC('month',create_timestamp)
FROM subscriber
| date_trunc |
|---|
| 2021-01-01 00:00:00 |
| 2021-02-01 00:00:00 |
| 2021-04-01 00:00:00 |
| 2021-04-01 00:00:00 |