Postgres. Generate continuous series for multiple categories I have a table that looks like this:

update_date  start_date   end_date   category_1 category_2    value
2013-03-04   2013-05-11  2013-06-08     a1           b1        23
2013-02-19   2013-03-16  2013-09-21     a2           b2       112
..


Now i want to transform the above table to this format:

date   category_1   category_2   value   update_date


For each pair of (category_1, category_2) I want to have a continuous series such as date is between start_date and end_date for each pair of (category_1, category_2) and keeping the respective value and update_date.

For example, for (a1, b1) I would have:

date       category_1  category_2   value   update_date
2013-05-11    a1          b1          23     2013-03-04
.             a1          b1          23     2013-03-04
.             a1          b1          23     2013-03-04
2013-06-08    a1          b1          23     2013-03-04


and this table would continue for each pair of (category_1, category_2).

I reckon there'd be duplicates of values in date column but i'm OK with that as long as there are no duplicates of (category_1, category_2) pair.

Anybody knows how to solve this?

Thank you in advance.
You can use generate_series() function:

select
    d.d::date as date, 
    t.update_date,  
    t.category_1, 
    t.category_2,
    t.value
from 
    t, 
    generate_series(start_date, end_date, interval '1 day') as d(d) ;


It works basically by taking each row from t and producing a set of rows, one for each day from start_date up to (and including) end_date.

Test at dbfiddle.uk