0
CREATE TABLE date0 AS
with num as (
select generate_series(0,cast('30-Mar-2022' as date) - cast('01-Jan-2022' as date)) "nm")
select cast('01-Jan-2022' as date) + nm AS invoice_date from num;

Why this code does not work in redshift? I want to create a table date0 with dates between two days.

However below code works fine. When I add the create table statement it fails.

with num as (
select generate_series(0,cast('30-Mar-2022' as date) - cast('01-Jan-2022' as date)) "nm")
select cast('01-Jan-2022' as date) + nm AS invoice_date from num;
Manoj Kumar
  • 176
  • 7
  • See the linked answer for a discussion of the limitations of `generate_series()` in Amazon Redshift and some workarounds. – John Rotenstein Feb 10 '22 at 08:09
  • sorry, I edited my question. generate_series is working just fine. – Manoj Kumar Feb 10 '22 at 08:16
  • See the linked question -- The `generate_series()` command only works on the Leader Node and cannot be combined with any table operations, which run on Compute Nodes. Your second code example runs because it is not interacting with any tables, but the first code example fails because it is using tables (specifically, it is creating a table). – John Rotenstein Feb 10 '22 at 08:28
  • hmm. Thanks for your answer. What is the solution then? I want to create table from the result of that with clause. – Manoj Kumar Feb 10 '22 at 08:42
  • Take a look at the examples on that page, especially [the one using recursion](https://stackoverflow.com/a/68946294/174777). It can generate a series of numbers that will work exactly the same way as `generate_series()`. For example, with a series of integers, you can use `DATE '01-Jan-2022' + n * interval '1 day'` to generate the series of dates you need. – John Rotenstein Feb 10 '22 at 09:16

0 Answers0