How to create a temp table in a Postgres query that contains a range of dates?

Question

I have some sparse date-related data in Postgres, and I want to extract a report that has a complete date range (even if there is no related data). I want to do the equivalent of a left outer join between a (non-existent) table of all dates in a range, and the data table that I have, i.e.

SELECT temp_dates.date, data_table.data_field
FROM temp_dates
LEFT OUTER JOIN data_table
ON temp_dates.date = data_table.data

My question is - how do I do create the temp table with the full range of dates?

NB I've asked about temp tables as I understand them, however if there is a better way (e.g. joining a table to an array, for instance), then I'd really like to know.

Craig Ringer · Accepted Answer · 2013-11-26 08:08:43Z

2

generate_series on PostgreSQL

get min date from your range then max and generate series

edited Nov 26, 2013 at 8:08

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

answered Nov 26, 2013 at 7:37

realnumber3012

1,0626 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hugo Rodger-Brown Over a year ago

Thanks for this - generate_series is exactly what I was looking for. Unfortunately I'm now getting unexpected results from the join. I will mark this as the answer and then open another question.

bugmenot123 Over a year ago

Please add an actual solution, not just a "look here for something you will use" link.

Collectives™ on Stack Overflow

How to create a temp table in a Postgres query that contains a range of dates?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related