Filling in missing dates in record set from generate_series()

This is some kind of misunderstanding. The query in your question already returns what you are asking for. I only changed minor details:

SELECT text 'Inspections'       AS data_label
     , count(i.close_case_date) AS daily_count
     , d.day                    AS date_column
FROM  (
   SELECT generate_series(timestamp '2013-01-01'
                        , timestamp '2013-01-01' + interval '1 year - 1 day'
                        , interval  '1 day')::date
   ) d(day)
LEFT   JOIN inspection i ON i.close_case_date = d.day
GROUP  BY d.day
ORDER  BY d.day;

About generating a series of dates:

• Generating time series between two dates in PostgreSQL

Minor points

• date '2013-01-01' + interval '1 year - 1 day' is better than date '2013-01-01' + 365 to also cover leap years.

• Using a cheaper subquery. No need for a CTE.

• Why count(i.reporting_id)? To just count rows in i, use count(i.close_case_date), which we already join to, so it cannot be NULL. count() only counts non-null values. If reporting_id can be NULL, you run the risk of not counting those rows.

Alternatively, move the cast, so we can use the set-returning function as table expression directly:

SELECT text 'Inspections'       AS data_label
     , count(i.close_case_date) AS daily_count
     , d.day::date              AS date_column
FROM   generate_series(timestamp '2013-01-01'
                     , timestamp '2013-01-01' + interval '1 year - 1 day'
                     , interval  '1 day') AS d(day)
LEFT   JOIN inspection i ON i.close_case_date = d.day::date
GROUP  BY d.day
ORDER  BY d.day;