Q:

Return "null" value if no record for date

I'm starting a query where i want to get all records between a time range. For Example:
select * from table where date between '2017-01-01' and '2017-01-31'

returns 31 records. But if there is no record for '2017-01-02' it will return 30 records.
Is it possible that for the missing date the query returns a null record to get 31 records too?
Data/Table
2017-01-01 | value1 | value2
2017-01-03 | value3 | value3

The select query should return
2017-01-01 | value1 | value2
null       | null   | null
2017-01-03 | value3 | value3

A:

You need a left join.  This is easy in Postgres, using generate_series():
select g.dte, t.col1, . . .
from generate_series('2017-01-1'::timestamp, '2017-01-31'::timestamp, interval '1 day') g(dte) left join
     table t
     on g.dte = t.date;

