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);
Best Answer
Cast the actual value, not the set.