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.

Answer 2

In modern Postgres (9.3+) it's best to use set-returning functions in a LATERAL join in the FROM list:

Assuming start_date and end_date are defined NOT NULL:

CREATE TABLE AS
SELECT name, location, day::date
FROM   tbl, generate_series(start_date, end_date, interval '1 day') day;

LATERAL is implicit here.
The manual about LATERAL subqueries.

Some related answers (there are many):

• Generate_series in Postgres from start and end date in a table
• Calculate working hours between 2 dates in PostgreSQL

About LATERAL (in reply to comment):

• What is the difference between LATERAL and a subquery in PostgreSQL?
• How to cast entity to set in PostgreSQL
• PostgreSQL unnest() with element number