Creating 'Empty' Records for Days of the Month Without Records

Question

I have a very simpl postgres (9.3) query that looks like this:

SELECT a.date, b.status
FROM sis.table_a a
JOIN sis.table_b b ON a.thing_id = b.thing_id
WHERE EXTRACT(MONTH FROM a.date) = 06
AND EXTRACT(YEAR FROM a.date) = 2015

Some days of the month of June do not exist in table_a and thus are obviously not joined to table_b. What is the best way to create records for these not represented days and assign a placeholder (e.g. 'EMPTY') to their 'status' column? Is this even possible to do using pure SQL?

possible duplicate of [Mark non-contiguous date ranges](http://stackoverflow.com/questions/5905064/mark-non-contiguous-date-ranges) — Politank-Z, Jun 26 '15 at 18:01
`cd.date` is in illegal reference. Please fix your sample code and provide Postgres version and the data type of your column `date`. Also: Does `table_b` always have all days? — Erwin Brandstetter, Jun 26 '15 at 18:08
Fixed. Table_b contains status codes, one of which applies to every day in table_a. — Preston, Jun 26 '15 at 18:19
Is `table_a.date` unique? And do you need anything else from `table_a` than the column `date`? — Erwin Brandstetter, Jun 26 '15 at 18:25
`table_a.date` is not unique. I'm also not trying to add these 'empty' references to the table, just want to return them with the query. `date` is the only feature I need to reference from `table_a`. — Preston, Jun 26 '15 at 18:27

score 1 · Accepted Answer · edited May 23 '17 at 11:51

Basically, you need LEFT JOIN and it looks like you also need generate_series() to provide the full set of days:

SELECT d.date
     , a.date IS NOT NULL AS a_exists
     , COALESCE(b.status, 'status_missing') AS status
FROM (
   SELECT date::date
   FROM   generate_series('2015-06-01'::date
                        , '2015-06-30'::date
                        , interval '1 day') date
    ) d
LEFT   JOIN sis.table_a a USING (date)
LEFT   JOIN sis.table_b b USING (thing_id)
ORDER  BY 1;

Use sargable WHERE conditions. What you had cannot use a plain index on date and has to default to a much more expensive sequential scan. (There are no more WHERE conditions in my final query.)

Aside: don't use the basic type name (and reserved word in standard SQL) date as identifier.

Related (2nd chapter):

PostgreSQL: running count of rows for a query 'by minute'

Creating 'Empty' Records for Days of the Month Without Records

1 Answers1