Redshift中如何将generate_series生成的日期序列插入表中？

问题原因 #

Redshift的generate_series是仅在leader节点运行的函数，而CREATE TABLE AS、INSERT这类涉及表写入的操作会分发到计算节点执行，计算节点不支持该函数，因此触发ERROR: Specified types or functions... not supported on Redshift tables错误。

可行解决方案 #

1. 使用递归CTE生成日期序列（推荐）

递归CTE在计算节点执行，不受leader节点函数限制，可灵活生成任意范围的日期序列：

-- 若生成超过1000天的序列，先调整递归深度（可选，默认递归深度为1000）
SET max_recursion_depth = 10000;

WITH recursive date_cte AS (
    -- 设置起始日期
    SELECT DATE '2020-01-01' AS date_range
    UNION ALL
    -- 逐天递增生成日期
    SELECT date_range + INTERVAL '1 day'
    FROM date_cte
    -- 设置结束条件（若需要包含结束日期，将<改为<=）
    WHERE date_range < DATE '2041-01-01'
)
CREATE TABLE public.date_sequence AS
SELECT date_range FROM date_cte;

2. 借助现有大表生成序列

如果集群中有行数足够多的表（比如系统表stl_scan，或业务中的大表），可以通过row_number()生成连续日期：

CREATE TABLE public.date_sequence AS
SELECT 
    DATE '2019-12-31' + (row_number() OVER (ORDER BY true)) * INTERVAL '1 day' AS date_range
FROM stl_scan
-- 限制生成的天数，此处为2020-01-01到2041-01-01的总天数
LIMIT (DATE '2041-01-01' - DATE '2020-01-01');

注意：需确保所选表的行数≥你需要的日期天数，否则会生成不完整的序列。

内容的提问来源于stack exchange，提问作者a_dwh_a