尝试创建日期序列yyyymmdd

Question

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

亚马逊无效操作：Redshift表不支持指定的类型或函数（每个信息消息一个）；1语句失败。

Answer 1

的确，generate_series应该不受Redshift支持（这里的Redshift文档说明了它不受支持），因此每当需要生成行时，必须动态创建它们（如下所示）：

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;

或者，您可以从某处选择行：

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

我很震惊地看到你发布的查询：

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