Redshift Integer Sequence Generator

Redshift Integer Sequence Generator.sql

-- Different ways to generate sequential Integer values in Redshift

-- Option 1: (The following statement is executed from leader node, 
--            hence any join operation from tables stored in compute node may cause query to fail.)
SELECT * FROM generate_series(0,100);

-- Generates sequence of integer by incrementing two
SELECT * FROM generate_series(0,100,2);

-- Generates positive sequence of integers upto 11,110
-- Can increase the digits by adding statements in the 'b' section
-- Option 2: (Works irrepctive of leader or compute node 
with a as (select 0 as x union all 
            select 1 union all 
            select 2 union all 
            select 3 union all
            select 4 union all
            select 5 union all
            select 6 union all
            select 7 union all
            select 8 union all
            select 9 ),
         b as (select a.x::text from a
        	   union all
			   (select  a.x::text || b.x::text from a cross join a as b)
               union all
			   (select  a.x::text || b.x::text || c.x::text from a cross join a as b cross join a as c)
			   union all
			   (select  a.x::text || b.x::text || c.x::text || d.x::text from a cross join a as b cross join a as c cross join a as d)           
			 )
select * from b


-- Option 3: Using recursive CTE, generated sequential values upto 50K.
WITH recursive numbers(n) as
( 
  SELECT 1 as n
    UNION all
    SELECT n + 1
    FROM numbers n
    WHERE n.n <= 50000
)
SELECT n FROM numbers;