Change a date range into new rows with discrete dates

Question

I have a table which contains name, location, start_date, and end_date. I am looking to transform these rows containing date ranges to rows of discrete dates while keeping corresponding row information intact.

Example:

Name1, Location1, 2015-4-01, 2015-4-04

Becomes:

Name1, Location1, 2015-4-01
Name1, Location1, 2015-4-02
Name1, Location1, 2015-4-03
Name1, Location1, 2015-4-04

I imagine that I'll need to create this as a new table using PostgreSQL functions.

Answer 1

Create a new table with the three required columns:

CREATE TABLE new_tbl (
  nam    varchar,
  loc    varchar,
  dt     date);

Since you want to have records in the new table over the range start_date - end_date with a day interval, inclusive, the easiest solution is to generate a set from your dates:

generate_series(start_date::timestamp, end_date::timestamp, '1 day')

This you can simply stick into an INSERT statement on the new table:

INSERT INTO new_tbl
  SELECT nam, loc, generate_series(start_date::timestamp, end_date::timestamp, '1 day')::date
  FROM old_tbl;

Since the generate_series function works with timestamp parameters, you need to explicitly cast your dates and then cast the generated timestamps back to dates to match your column definition.