A lot of charts, tables and dashboards that are developed using series values such as time series. Amazon Redshift prior to the PostgreSQL 8.4 was not supported generate series function. In this article, we will check how to use Redshift generate_series function, its usage and example.
Page Content
Introduction
Redshift generate_series function is a powerful tool that is widely used in data analysis, reporting, and generating test data. This function allows you to generate a series of sequential numbers or dates that can be used to perform various data manipulation tasks in Redshift. It is a versatile function that can be used to generate various sequences and can be used in a wide range of applications. In this post, we will explore the Redshift generate_series function in depth, including its syntax, parameters, applications. We will also provide several examples to illustrate its usage in real-world scenarios. By the end of this post, you will have a solid understanding of the Redshift generate_series function and how you can leverage it in your data analysis and reporting tasks.
Redshift generate_series Function
The version of generate_series() function that supports dates and timestamps was added in Postgres 8.4. As mentioned earlier, series function is used to generate series such as integer, time series, etc.
Redshift generate_series Function Syntax
There is no documentation available for generate series function in Redshift. Below is the syntax of PostgreSQL generate series function, the same will work with Redshift as well.
generate_series(start, stop, step)Generate a series of values, from start to stop with a step size of step.
Redshift generate_series Function Example
Below is the example to generate integer series. For example, generate sequential series for integer number starting from 1.
dev=# select generate_series(1,3);
generate_series
-----------------
1
2
3
(3 rows)Generate Series in Reverse Order
You can generate a series in reverse order as well. For example, below example generates series values in reverse order.
select * from generate_series(5, 1, -1);
generate_series
-----------------
5
4
3
2
1
(5 rows)How to Generate Date Series in Redshift?
Below example lets you to generate 10 days series that you can use in your dashboards and KPI’s.
SELECT CURRENT_DATE::TIMESTAMP - (i * interval '1 day') as date_datetime
FROM generate_series(1,10) i
ORDER BY 1
date_datetime
---------------------
2019-09-27 00:00:00
2019-09-28 00:00:00
2019-09-29 00:00:00
2019-09-30 00:00:00
2019-10-01 00:00:00
2019-10-02 00:00:00
2019-10-03 00:00:00
2019-10-04 00:00:00
2019-10-05 00:00:00
2019-10-06 00:00:00
(10 rows)Generate Series Function with INSERT Statement
You cannot use this function with the Redshift insert command, the reason why this wouldn’t let you insert any rows to your table, is that, this is a Leader-Node-Only function, whereas INSERTs are run on the Compute Nodes. The compute node does not aware of this function.
For example, consider below of insert command.
dev=# insert into test select * from(select generate_series(1,3) as col1) as a;
INFO: Function "generate_series(integer,integer)" not supported.
ERROR: Specified types or functions (one per INFO message) not supported on Redshift tables.As you can see in above example, generate_series function is not recognized in compute nodes. You cannot use this function in combination with compute node functions.
Conclusion
In conclusion, the Redshift generate_series function can be used in a wide range of applications, from data analysis and reporting to generating test data. By allowing you to generate a sequence of sequential numbers or dates, this function can be used to perform a variety of data manipulation tasks.
Related Articles,
Hope this helps 🙂