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