CODEWITHSUNDEEP
sqlamazon-redshift
Ashutosh SIngh
Ashutosh SIngh

Reputation: 1019

Unable to insert data using generate_series() in redshift

I'm trying to insert all dates for next 10 days using below query.

INSERT INTO sda.all_dates (checkin)
SELECT CURRENT_DATE +  generate_series(0, 10) AS checkin;

but unable to do, getting error

INFO:  Function "generate_series(integer,integer)" not supported.
ERROR:  Specified types or functions (one per INFO message) not supported on Redshift tables.

when i execute only SELECT part it give correct result

SELECT CURRENT_DATE +  generate_series(0, 10) AS checkin;

Upvotes: 1

Views: 1614

Answers (4)

Med Zamrik
Med Zamrik

Reputation: 323

To work around this limitation, I exported the output of the query that contains generate_series() to my localhost, uploaded it to redshift and inserted the data through a copy command. I tried using unload(), but that command also fails when you're using generate_series().

Upvotes: 0

AlexYes
AlexYes

Reputation: 4208

Generators are not supported in Redshift, as pointed above.

You have 2 options:

1) a small hack like this: 

with
series as (
    select 1 as n
    union select 2
    union select 3
)
select (current_date+interval '1 day'*n)::date
from series

2) create a dimensional table that stores every date of the calendar so you'll have something like this:

select date 
from dim.calendar 
where date between current_date and current_date+interval '10 day'

you can create such table by running generate_series() in Postgres, copying the result to CSV and loading that CSV to Redshift, it has to be done once. Besides dates, you can have different derivatives from date (first date of the week, month, quarter, etc.) depending on your goals.

UPD: Redshift supports generate_series() now

Upvotes: 1

Rahul Gupta
Rahul Gupta

Reputation: 1802

When you execute only SELECT, the query is executed on leader node since no table scan is required, that is why you get the correct result.

But when you involve redshift tables (like INSERT INTO) the query with generate_series() is executed on compute node, which is not a supported function in redshift.

If I’ve made a bad assumption please comment and I’ll refocus my answer.

Upvotes: 2

Yusuf Hassan
Yusuf Hassan

Reputation: 2013

Amazon Redshift doesn't support what you're looking for.

Refer : Amazon Redshift Documentation for unsupported functions

generate_series() is one of the many postgres functions not supported by Amazon Redshift

Upvotes: 1

Related Questions