3

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?

Zakir Hossain
  • 440
  • 5
  • 20

1 Answers1

5

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?

Why the cast to timestamp?

Why LEFT JOIN .. ON true?

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • #ErwinBrandstetter, sorry i found syntax error after the ARRAY key word, i am using postgreSQL 9.1. is this query valid for this version ? – Zakir Hossain Nov 01 '18 at 07:07
  • @ZakirHossain: `LATERAL` requires Postgres 9.3 or later. I added an alternative. – Erwin Brandstetter Nov 01 '18 at 14:36
  • @ Erwin Brandstetter: output fine but i need array's value's column position. That mean i get the value "{2018-10-01,2018-10-02,2018-10-03,2018-10-04,2018-10-05}" but i need each value's column position like | 2018-10-01 | |2018-10-02| |2018-10-03| |2018-10-04| |2018-10-05 | because i want to compare this column with another column value. – Zakir Hossain Nov 02 '18 at 02:33
  • *Updated to fix misleading typo:* Postgres does not allow a dynamic number of result columns. You can only achieve this with a two-step workflow, like I wrote under your other question: https://stackoverflow.com/a/53094035/939860 (And it's impossible on principle for multiple rows to disagree on the number of columns.) – Erwin Brandstetter Mar 31 '21 at 19:34