This Question have no Text answers yet!

Using sql function generate_series() in redshift

By : Elm
Source: Stackoverflow.com
Question!

I'd like to use the generate series function in redshift, but have not been successful.

The redshift documentation says it's not supported. The following code does work:

select *
from generate_series(1,10,1)

outputs:

1
2
3
...
10

I'd like to do the same with dates. I've tried a number of variations, including:

select *
from generate_series(date('2008-10-01'),date('2008-10-10 00:00:00'),1)

kicks out:

 ERROR: function generate_series(date, date, integer) does not exist
 Hint: No function matches the given name and argument types.
 You may need to add explicit type casts. [SQL State=42883]

Also tried:

select *
from generate_series('2008-10-01 00:00:00'::timestamp,
'2008-10-10 00:00:00'::timestamp,'1 day')

And tried:

select *
from generate_series(cast('2008-10-01 00:00:00' as datetime),
cast('2008-10-10 00:00:00' as datetime),'1 day')

both kick out:

ERROR: function generate_series(timestamp without time zone, timestamp without time zone, "unknown") does not exist
Hint: No function matches the given name and argument types.
You may need to add explicit type casts. [SQL State=42883]

If not looks like I'll use this code from another post:

SELECT to_char(DATE '2008-01-01'
+ (interval '1 month' * generate_series(0,57)), 'YYYY-MM-DD') AS ym

PostgreSQL generate_series() with SQL function as arguments

By : Elm


Video about Using sql function generate_series() in redshift