redshift - how to insert into table generated time series

Question

I am trying to generate time series in Redshift and insert into table, but no luck. What I have tried so far:

insert into date(dateid,date)
SELECT
    to_char(datum, 'YYYYMMDD')::int AS dateid,
    datum::date AS date
FROM (
    select '1970-01-01'::date + generate_series(0, 20000) as datum
     ) tbl;

Getting the following error

SQL Error [500310] [0A000]: [Amazon](500310) Invalid operation: Specified types or functions (one per INFO message) not supported on Redshift tables.;

Any ideas or workaround ?

score 3 · Answer 1 · answered Mar 11 '19 at 01:27

The issue is that generate_series() can be run on the Leader node, but not on a compute node.

Therefore, it is possible to run a statement like this:

SELECT '1970-01-01'::date + generate_series(0, 20000)

However, it is not possible to use that statement in a FROM because that would involve the compute nodes.

Solution: Create a table of information externally and load the results into a date table, or use generate_series() directly to generate the desired values, save the results and import them into a date table.

I basically wrote a Talend job which selects using generate_series() and dynamically insets into the table — bstent, Mar 11 '19 at 16:01

score 2 · Answer 2 · answered Mar 11 '19 at 07:44

The generate_series function does not work on Redshift

it does however generate_confusion! This is because it looks like its working fine until you try to write to a table.

Technically, the reason for this is that it works ONLY on the leader node. and the leader node itself does not have access to the tables directly.

If you try

SELECT CURRENT_DATE::TIMESTAMP - (i * interval '1 day') as date_datetime
FROM generate_series(1,31) i
ORDER BY 1;

That produces a nice table output, because it has run on the leader node.

HOWEVER if you run

create table test as
SELECT CURRENT_DATE::TIMESTAMP - (i * interval '1 day') as date_datetime
FROM generate_series(1,31) i
ORDER BY 1;

This fails with

[2019-03-11 07:38:00] [0A000] ERROR: Specified types or functions (one per INFO message) not supported on Redshift tables. [2019-03-11 07:38:00] [00000] Function "generate_series(integer,integer)" not supported.

This is because we are now trying to create a table, so the sql has to be run on the worker nodes (not the leader node) and so it fails.

. . If you put the `generate_series()` in a CTE, does that work? — Gordon Linoff, Mar 11 '19 at 10:31
No, it doesn't - in redshift a cte is just like a subquery (unlike postgres). (i did test it just in case - same error) — Jon Scott, Mar 11 '19 at 10:33

score 0 · Answer 3 · answered Jun 13 '22 at 08:25

As a workaround, you may generate the integer sequence using a query like the following:

WITH
digits AS (
    SELECT 0 AS v
    UNION SELECT 1
    UNION SELECT 2
    UNION SELECT 3
    UNION SELECT 4
    UNION SELECT 5
    UNION SELECT 6
    UNION SELECT 7
    UNION SELECT 8
    UNION SELECT 9
), 
seq AS (
    SELECT 10000*tens_of_thousands.v + 1000*thousands.v + 100*hundreds.v + 10*tens.v + units.v AS seq
    FROM digits as units
    CROSS JOIN digits AS tens
    CROSS JOIN digits AS hundreds
    CROSS JOIN digits AS thousands
    CROSS JOIN digits AS tens_of_thousands
    ORDER BY seq
)
SELECT * FROM seq;

redshift - how to insert into table generated time series

3 Answers3

Linked