CODEWITHSUNDEEP
sqljoinamazon-redshift
Cauder
Cauder

Reputation: 2567

How do I complete a cross join with redshift?

I have two tables. One has a user ID and a date and another has a list of dates.

with first_day as (
select '2020-03-01' AS DAY_CREATED, '123' AS USER_ID
),

date_series as (
SELECT ('2020-02-28'::date + x)::date as day_n,
'one' as join_key
FROM generate_series(1, 30, 1) x
)

SELECT * from first_day cross join date_series

I'm getting this error with redshift Error running query: Specified types or functions (one per INFO message) not supported on Redshift tables.

Can I do a cross join with redshift?

Upvotes: 1

Views: 4544

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Alas, Redshift supports generete_series() but only in a very limited way -- on the master processing node. That basically renders it useless.

Assuming you have a table with enough rows, you can use that:

with first_day as (
      select '2020-03-01' AS DAY_CREATED, '123' AS USER_ID
     ),
     date_series as (
      select ('2020-02-28'::date + x)::date as day_n,
             'one' as join_key
      from (select t.*, row_number() over () as x
            from t   -- big enough table
            limit 30
           ) x
select *
from first_day cross join 
     date_series;

Upvotes: 3

Related Questions