2

I got to wondering about how we would best use generate_series if we only want a series of dates. As a note, generate_series returns setof timestamp or setof timestamp with time zone (same as argument type). For example, observe this..

SELECT generate_series(
  '2018-01-01'::date,
  '2019-01-01'::date,
  '1 day'
);
    generate_series     
------------------------
 2018-01-01 00:00:00-06
 2018-01-02 00:00:00-06
 2018-01-03 00:00:00-06

In the above you'll notice the time portion. In order to generate dates, I tried,

SELECT cast(
  generate_series(
    '2018-01-01'::date,
    '2019-01-01'::date,
    '1 day'
  )
  AS date
);
 generate_series 
-----------------
 2018-01-01
 2018-01-02
 2018-01-03

To my surprise that worked! But, that raises the question, is this supported? If it is, is this documented. Even more awkward, in the above you'll see that cast is returning a setof, but if you move it to the from condition like this,

SELECT *
FROM cast(                                                     
  generate_series(
    '2018-01-01'::date,
    '2019-01-01'::date,
    '1 day'
  )
  AS date
);

You'll get back,

ERROR: set-valued function called in context that cannot accept a set

As a side note, it works in sub-selects and the dynamic can also be seen with int/reals

SELECT x/5 FROM (SELECT CAST(generate_series(1,10) AS real)) AS t(x);
SELECT x/5 FROM (SELECT CAST(generate_series(1,10) AS int)) AS t(x);

Question inspired from this answer

  • Yet more examples: select generate_series(1,3) + 10; or select exp(generate_series(1,3)); – Abelisto Jun 11 '17 at 6:45
1

Cast the actual value, not the set.

SELECT v::date
FROM generate_series('2018-01-01'::date, '2019-01-01'::date, '1 day') as t(v);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.