Filling missing dates in each group while querying data from PostgreSQL

I have data in my table of the following form:

| date     | type | value |
+----------+------+-------+
|2020-01-01| A    |     29|
|2020-02-01| A    |     32|
|2020-04-01| A    |     56|
|2020-05-01| A    |     78|
|2020-01-01| B    |     12|
|2020-02-01| B    |     89|
|2020-03-01| B    |     44|
|2020-05-01| B    |     33|

Now while querying this data, I want the missing dates to be filled with null values.

In PostgreSQL, I can use generate_series() to create a series of dates and then left join with it. But that only works if I have just a series of dates and values. In this case, since I have two types, I don’t really have any missing dates in the series as a whole, but I have missing dates in each group/partition.

Is it possible to use generate_series() with left join to fill rows with null in this case?

Answer

You may cross join with a table which contains all types, and then use the same left join approach you were already considering:

SELECT
    date_trunc('day', cal)::date AS date,
    t1.type,
    t2.value
FROM generate_series
    ( '2020-01-01'::timestamp 
    , '2020-12-31'::timestamp
    , '1 day'::interval) cal
CROSS JOIN (SELECT DISTINCT type FROM yourTable) t1
LEFT JOIN yourTable t2
    ON t2.date = cal.date AND t2.type = t1.type
ORDER BY
    t1.type,
    cal.date;