1

I ran into a problem with Redshift. I'm generating a sequence of dates and want to embed it in a table to work with the range. But Redshift only supports generation on the leader node. It is not possible to insert the data on the nodes. Nowhere in the documentation have I found information on how to insert the generated sequences into tables. Maybe someone has encountered such a problem and can share their experience in solving it? My sequence:

SELECT date '2019-12-31' + INTERVAL AS date_range
  FROM generate_series(1, (date '2041-01-01' - date '2020-01-01')) INTERVAL;

My query:

CREATE TABLE public.date_sequence AS (                
SELECT date '2019-12-31' + INTERVAL AS date_range
  FROM generate_series(1, (date '2041-01-01' - date '2020-01-01')) INTERVAL
);

I also tried inserting data from cte. Insert data into a temporary table. The result is the same: ERROR: Specified types or functions (one per INFO message) not supported on Redshift tables.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
a_dwh_a
  • 11
  • 2

2 Answers2

1

In amazon redshift you cant populate a table with data with create table you have to use a insert as far as I know, my suggestion would be something like this

INSERT INTO public.date_sequence
SELECT date '2019-12-31' + INTERVAL AS date_range
FROM generate_series(1, (date '2041-01-01' - date '2020-01-01')) INTERVAL;
Breezer
  • 10,410
  • 6
  • 29
  • 50
0

Generate_series() is an unsupported function on Redshift. While it will still work on the leader node it is not a good idea to base your solutions on such functions.

The better and supported approach is to use a recursive CTE - see https://docs.aws.amazon.com/redshift/latest/dg/r_WITH_clause.html

I wrote up an example for making a series of dates in this answer - trying to create a date table in Redshift

Bill Weiner
  • 8,835
  • 2
  • 7
  • 18