Trying to create a date sequence yyyymmdd

Question

I'm trying to create a date range sequence in the following format yyyymmdd

insert into dim_SH (date_key)
SELECT to_char(('2012-01-01'::date + x)::date,'yyyymmdd') 
FROM generate_series(1, 365*15, 1) x;

Amazon Invalid operation: Specified types or functions (one per INFO message) not supported on Redshift tables.; 1 statement failed.

Answer 1

It's true that GENERATE_SERIES is supposedly not supported by Redshift (here's Redshift documentation saying that it is not supported) and therefore whenever you need to generate rows, you have to create them on the fly (like below):

WITH ten as (
  select 1 union all
  select 1 union all
  select 1 union all
  select 1 union all
  select 1 union all
  select 1 union all
  select 1 union all
  select 1 union all
  select 1 union all
  select 1
), ten_k as (
  select row_number() over () as x
  from ten a cross join
     ten b cross join
     ten c cross join
     ten d
  )
SELECT to_char(('2012-01-01'::date + x)::date,'yyyymmdd')
FROM ten_k limit 365*15;

Alternatively you can select rows from somewhere:

SELECT x FROM (SELECT row_number() over () as x FROM arbitrary_table limit 10000) as ten_k

---

Is GENERATE_SERIES really not supported?

I was shocked to see that the query you posted:

SELECT to_char(('2012-01-01'::date + x)::date,'yyyymmdd')
FROM generate_series(1, 365*15, 1) x;

actually worked when ran against our Redshift (in version 1.0.7078). Release notes do not mention any new functionality in that area [:puzzled:].

EDIT: Apparently as of this question, generate_series works on leader node only, but fails whenever your query need to reach the compute nodes.

Hence, not fully supporting this function deems it unsupported.