3

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Bud
  • 33
  • 3

2 Answers2

3

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.

Patrick
  • 29,357
  • 6
  • 62
  • 90
3

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):

About LATERAL (in reply to comment):

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • When you say "it's best to use set-returning functions in a LATERAL join", do you mean that that is the preferred way of doing things, or is there a performance / portability / etc issue here? – Patrick May 03 '15 at 04:16
  • @Patrick: Set-returning functions in the `SELECT` list are a totally non-standard Postgres extension. They work fine for simple cases but exhibit dubious behavior when combining multiple instances or in some corner cases. `LATERAL` is standard SQL syntax and provides clean solutions for all combinations. It allows new features like `WITH ORDINALITY`. Performance for basic cases is similar. I added some links to the answer. – Erwin Brandstetter May 03 '15 at 04:39