Postgres - Inject 0 values when rows do not exist I have 3 tables that I need for reporting:


    *dates*         
date_sk | full_date  | day_number_of_month  
1       | 2013-01-01 |       1          
2       | 2013-02-01 |       1  
3       | 2013-03-01 |       1
4       | 2013-02-02 |       2
5       | 2013-02-03 |       3

    *person*            
person_sk   | person_id  | person_name      
1           |   10       |   John       
2           |   11       |   Bob        
3           |   12       |   Jill       



    *person_portfolio*          
person_portfolio_sk | date_sk | person_sk | res_value | report_month
1                   |   1     |     1     |     15    |  2013-01-01
2                   |   1     |     2     |     10    |  2013-01-01
3                   |   1     |     3     |      1    |  2013-01-01
4                   |   2     |     1     |     30    |  2013-02-01


(imagine the 'dates' table filled with every date for the past 10 and next 10 years which includes every day of every month. My reporting is for a monthly level which is why i need day_number_of_month = 1 for the first of every month)

I have been struggling to find out, for comparison reporting purposes using a date range, how to replace no entries during that timeframe with 0 values for the person. Here is the query I have tried:

SELECT
 p.person_id,
 COALESCE(pp.res_value,0)::NUMERIC(16,2) AS res_value,
 pp.report_month
FROM person p
LEFT JOIN person_portfolio pp
ON p.person_sk = pp.person_sk
LEFT JOIN date d
ON d.date_sk = pp.date_sk
WHERE person_id IN ('10','11','12')
AND pp.report_month &gt;= '2013-01-01' --From Date
AND pp.report_month &lt;= '2013-05-01' -- To Date
AND d.day_number_of_month = 1
ORDER BY p.person_id DESC;


The output I want to return would end up being 15 rows total. 3 people x 5 months of data = 15 total rows. It should look like this:


person_id   | res_value | report_month
10          |   15      |   2013-01-01
10          |   30      |   2013-02-01
10          |   0       |   2013-03-01
10          |   0       |   2013-04-01
10          |   0       |   2013-05-01
11          |   10      |   2013-01-01
11          |   0       |   2013-02-01
11          |   0       |   2013-03-01
11          |   0       |   2013-04-01
11          |   0       |   2013-05-01
12          |   1       |   2013-01-01
12          |   0       |   2013-02-01
12          |   0       |   2013-03-01
12          |   0       |   2013-04-01
12          |   0       |   2013-05-01


but I am only getting these results:


person_id   | res_value | report_month
10          |   15      |  2013-01-01
10          |   30      |  2013-02-01
11          |   10      |  2013-01-01
12          |    1      |  2013-01-01


So basically... is there currently a feasible way that I could inject the 0 value rows into the results when there is no entry for the 'report_month' for a specific person(s)? I would appreciate any kind of help as I have been working on this for 2 weeks now trying to complete this report. Thanks!
You need to create a pseudo date range and join to it...  Like such:

select generate_series('2014-01-01'::timestamp, '2014-10-01'::timestamp, '1 month'::interval)::date;


Once you have your series, you can join to it and use COALESCE to fill in 0's for where there is no data..

WITH days AS (
  select generate_series('2014-01-01'::timestamp, '2014-10-01'::timestamp, '1 month'::interval)::date AS day
) SELECT
  d.day, COALESCE(t1.col1, 0) AS value
FROM days d
LEFT JOIN &lt;something with a date&gt; t1 on d.day = t1.day


If you need to join to something that DOESN'T have a date (for example, your "person" table above), then just join with the condition being "TRUE" (to make a cartesian join, so every person gets every day)

WITH days AS (
  select generate_series('2014-01-01'::timestamp, '2014-10-01'::timestamp, '1 month'::interval)::date AS day
) SELECT
  d.day, COALESCE(t1.col1, 0) AS value
FROM days d
JOIN person p ON TRUE