[SOLVED] Can't create a temp table using random data in redshift?

Can't create a temp table using random data in redshift?

I'm trying to create a simple temp table of dates and random numbers.

This works:

select * from
    (select now()::date - generate_series(0, 29) as date,
       CAST(RANDOM() * 10 AS INT)  as nmb) a;

...but for some reason this doesn't:

create temp table nmbr_dates as
    (select GETDATE() - generate_series(0, 29) as date,
       CAST(RANDOM() * 10 AS INT)  as nmb);

It returns the following error: [0A000] ERROR: Specified types or functions (one per INFO message) not supported on Redshift tables.

Googling tells me the problem is due to functions being located on different nodes, which is not a problem I've dealt with before. Can anyone tell me if there's an easy workaround?

Solution

As others have noted generate_series() is a leader-only function on Redshift and cannot be (easily) used to populate data on compute nodes. On Redshift you will want to use a recursive CTE to generate a series of dates.

This question comes up a lot on SO so here' a link to a previous answer - Date hour minute table

The AWS docs on performing this are at - https://docs.aws.amazon.com/redshift/latest/dg/r_WITH_clause.html

If this isn't clear enough or you need further help just comment.