CODEWITHSUNDEEP
sqlamazon-redshift
Rahul
Rahul

Reputation: 893

can't use JOIN with generate_series on Redshift

generate_series function on Redshift works as expected, when used in a simple select statement. 

WITH series AS (
    SELECT n as id from generate_series (-10, 0, 1) n
) SELECT * FROM series;
-- Works fine

As soon as I add a JOIN condition, redshift throws

com.amazon.support.exceptions.ErrorException: Function generate_series(integer,integer,integer)" not supported"

DROP TABLE testing;
CREATE TABLE testing (
  id INT
);
WITH series AS (
  SELECT n as id from generate_series (-10, 0, 1) n
) SELECT * FROM series S JOIN testing T ON S.id = T.id;
-- Function "generate_series(integer,integer,integer)" not supported.

Redshift Version

SELECT version();
-- PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.1485

Are there any workarounds to make this work?

Upvotes: 7

Views: 3493

Answers (3)

Jim
Jim

Reputation: 638

The right way to generate rows with redshift is to use WITH RECURSIVE CTEs, like that:

with recursive t(n) as (
  select 0::integer
  union all
  select n+1 from t where n <= 100
)
select n*2 as two_times_n from t;

You can join t with whatever real table you want.

See https://docs.aws.amazon.com/redshift/latest/dg/r_WITH_clause.html

Upvotes: 0

AlexYes
AlexYes

Reputation: 4208

generate_series is not supported by Redshift. It works only standalone on a leader node.

A workaround would be using row_number against any table that has sufficient number of rows:

with 
series as (
    select (row_number() over ())-11 from some_table limit 10
) ...

also, this question was asked multiple times already

Upvotes: 4

Jon Scott
Jon Scott

Reputation: 4354

You are correct that this does not work on Redshift. See here.

The easiest workaround is to create a permanent table "manually" beforehand with the values within that table, e.g. you could have rows on that table for -1000 to +1000, then select the range from that table,

So for your example you would have something like 

WITH series AS (
  SELECT n as id from (select num as n from newtable where num between -10 and 0) n
) SELECT * FROM series S JOIN testing T ON S.id = T.id;

Does that work for you?

Alternatively, if you cannot create the table beforehand or prefer not to, you could use something like this

with ten_numbers as (select 1 as num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0)
,generted_numbers AS
(
    SELECT (1000*t1.num) + (100*t2.num) + (10*t3.num) + t4.num-5000 as gen_num
    FROM ten_numbers AS t1
      JOIN ten_numbers AS t2 ON 1 = 1
      JOIN ten_numbers AS t3 ON 1 = 1
      JOIN ten_numbers AS t4 ON 1 = 1
)
select  gen_num from generted_numbers
where gen_num between -10 and 0
order by 1;

Upvotes: 2

Related Questions