How to show all dates from a certain date range in horizontal row?

Question

I have database table in PostgreSQL named as t1 like:

Name	StartDate	EndDate
Oct-18	2018-10-01	2018-10-05

I want the result for the date range like:

Oct-18	2018-10-01	2018-10-02	2018-10-03	2018-10-04	2018-10-05

with the help of generate_series() I can do it "vertically", but how to get the result in a single row?

Answer 1

Use generate_series(). But SQL does not allow a dynamic number of result columns. So you must wrap your result in a string, array or document type to make it work. Example with an ARRAY constructor in a LATERAL subquery - in Postgres 10 or later:

SELECT t1.name, d.date_arr::date[]
FROM   t1
LEFT   JOIN LATERAL (
   SELECT ARRAY(SELECT generate_series(t1.startdate::timestamp
                                     , t1.enddate::timestamp
                                     , interval '1 day'))
   ) d(date_arr) ON true;

Why (preferably) Postgres 10 or later?

• What is the expected behaviour for multiple set-returning functions in SELECT clause?

Why the cast to timestamp?

• Generating time series between two dates in PostgreSQL

Why LEFT JOIN .. ON true?

• What is the difference between LATERAL JOIN and a subquery in PostgreSQL?

Though LEFT JOIN is not necessary in this particular case (could be CROSS JOIN) because the ARRAY constructor always returns a row.

Postgres 9.1

LATERAL requires Postgres 9.3 or later. You can substitute with a correlated subquery:

SELECT name
     , ARRAY(SELECT generate_series(startdate::timestamp
                                  , enddate::timestamp
                                  , interval '1 day')::date)
FROM   t1;

Even works with pg 8.4:

db<>fiddle here

But consider upgrading to a current version.

crosstab()?

crosstab() cannot overcome the static nature of SQL, either. There are limited workarounds with prepared row types ...

• Dynamic alternative to pivot with CASE and GROUP BY
• Dynamically generate columns for crosstab in PostgreSQL
• PostgreSQL Crosstab Query