Using sql function generate_series() in redshift

by

Amazon Redshift seems to be based on PostgreSQL 8.0.2. The timestamp arguments to generate_series() were added in 8.4.

Something like this, which sidesteps that problem, might work in Redshift. 

SELECT current_date + (n || ' days')::interval
from generate_series (1, 30) n

It works in PostgreSQL 8.3, which is the earliest version I can test. It’s documented in 8.0.26.

Later . . .

It seems that generate_series() is unsupported in Redshift. But given that you’ve verified that select * from generate_series(1,10,1) does work, the syntax above at least gives you a fighting chance. (Although the interval data type is also documented as being unsupported on Redshift.)

Still later . . .

You could also create a table of integers.

create table integers (
  n integer primary key
);

Populate it however you like. You might be able to use generate_series() locally, dump the table, and load it on Redshift. (I don’t know; I don’t use Redshift.)

Anyway, you can do simple date arithmetic with that table without referring directly to generate_series() or to interval data types.

select (current_date + n)
from integers
where n < 31;

That works in 8.3, at least.

More Related Contents:

  1. What is the expected behaviour for multiple set-returning functions in SELECT clause?
  2. Best way to count rows by arbitrary time intervals
  3. Calculate working hours between 2 dates in PostgreSQL
  4. How do I view grants on Redshift
  5. How to get the last day of month in postgres?
  6. How to perform a select query in a DO block?
  7. Join a count query on generate_series() and retrieve Null values as ‘0’
  8. I dont know sql syntax for my table [closed]
  9. Get top 1 row of each group
  10. Entity Attribute Value Database vs. strict Relational Model Ecommerce
  11. Why is SQL Server losing a millisecond?
  12. Find a string by searching all tables in SQL Server
  13. Common Table Expression, why semicolon?
  14. T-SQL split string based on delimiter
  15. Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?
  16. How do I find duplicates across multiple columns?
  17. Query comparing dates in SQL
  18. Mysql: how to select groups having certain values?
  19. Return setof record (virtual table) from function
  20. Given an RGB value what would be the best way to find the closest match in the database?
  21. How to export image field to file?
  22. OVER clause in Oracle
  23. PARTITION BY with and without KEEP in Oracle
  24. Stored procedure that exports data into csv files only exports to one file
  25. Get Start and End Date from Week Number SQL Server
  26. PostgreSQL: Give all permissions to a user on a PostgreSQL database
  27. How to create a Date in SQL Server given the Day, Month and Year as Integers
  28. Count records for every month in a year
  29. What is the best way to implement soft deletion?
  30. Postgres: convert single row to multiple rows (unpivot)

Leave a Comment