How to pad data with empty values ​​(postgres)?

Question

PostgreSQL

I have a query that retrieves data on statistics for a certain period of time.

SELECT product_count, created_at::date FROM statistic WHERE created_at::date >= '2015-05-05' AND created_at::date <= '2015-05-08' GROUP BY created_at::date

If I have data only for 2 dates, for example, for 2015-05-05 and 2015-05-06, and there is no data for other dates.
At the end I will get

product_count | created_at
----------------------------------
1                  | 2015-05-05
3                  | 2015-05-06

I need to fill in the rest of the data with zeros to get the output like:

product_count | created_at
----------------------------------
1                  | 2015-05-05
3                  | 2015-05-06
2                  | 2015-05-07
5                  | 2015-05-08

How to make such a request?

Answer 1

what for to group, if in the table on 1st record for a day?
and grouping without an aggregate function will break at the first duplicate of the date
for the case of 1 record / day, you can do this:

select coalesce(product_count,0), dt::date from generate_series('2015-05-05'::date, '2015-05-08', '1 day') dt
left join statistic on created_at::date = dt::date