Search code examples
sqlpostgresqlsplit

Split column and values into multiple rows in Postgres

Suppose I have a table like this:

subject flag first_date last_date
this is a test 2 1/1/2016 1/4/2016

into something like this:

subject flag date
this is a test .5 1/1/2016
this is a test .5 1/2/2016
this is a test .5 1/3/2016
this is a test .5 1/4/2016

Is there an easy way to do this?

Solution

  • You can use generate_series() to produce list of consecutive days between first_date and last_date:

    with dates as (
    select d::date, last_date- first_date+ 1 ct
    from test, generate_series(first_date, last_date, '1d'::interval) d
    )
select subject, flag/ ct flag, d date
from dates
cross join test;

    subject     |          flag          |    date    
----------------+------------------------+------------
 this is a test | 0.50000000000000000000 | 2016-01-01
 this is a test | 0.50000000000000000000 | 2016-01-02
 this is a test | 0.50000000000000000000 | 2016-01-03
 this is a test | 0.50000000000000000000 | 2016-01-04
(4 rows)