When I run the SQL Query:
select generate_series(0,g)
from ( select date(date1) - date(date2) as g from mytable ;
It returns an error:
INFO: Function "generate_series(integer,integer)" not supported.
ERROR: Specified types or functions (one per INFO message) not supported
on Redshift tables.
But when I run this query:
select generate_series(0, g) from (select 5 as g)
It returns the below response:
generate_series
-----------------
0
1
2
3
4
5
(6 rows)
Why does the second query work, while the first fails?
The generate_series() function is not fully supported by Redshift. See the Unsupported PostgreSQL functions section of the developer guide:
In the specific examples, the second query is executed entirely on the leader node as it does not need to scan any actual table data, while the first is trying to select data and as such would be executed on the compute node(s).
UPDATE:
generate_series is working with Redshift now.
SELECT CURRENT_DATE::TIMESTAMP - (i * interval '1 day') as date_datetime
FROM generate_series(1,31) i
ORDER BY 1
This will generate date for last 30 days