generate_series() 方法在 Redshift 中失败 [英] generate_series() method fails in Redshift

问题描述

当我运行 SQL 查询时:

When I run the SQL Query:

 select generate_series(0,g)
 from ( select date(date1) - date(date2) as g from mytable ;

它返回一个错误:

 INFO:  Function "generate_series(integer,integer)" not supported.
 ERROR:  Specified types or functions (one per INFO message) not supported 
 on Redshift tables.

但是当我运行这个查询时:

But when I run this query:

select  generate_series(0, g) from (select 5 as g)

它返回以下响应:

 generate_series
-----------------
 0
 1
 2
 3
 4
 5
(6 rows)

为什么第二个查询有效，而第一个查询失败?

Why does the second query work, while the first fails?

推荐答案

Redshift 不完全支持 generate_series() 函数.请参阅不支持的 PostgreSQL 函数开发者指南的部分:

The generate_series() function is not fully supported by Redshift. See the Unsupported PostgreSQL functions section of the developer guide:

在具体示例中，第二个查询完全在领导节点上执行，因为它不需要扫描任何实际表数据，而第一个查询尝试选择数据，因此将在计算节点上执行).

In the specific examples, the second query is executed entirely on the leader node as it does not need to scan any actual table data, while the first is trying to select data and as such would be executed on the compute node(s).

更新:

generate_series 现在正在使用 Redshift.

generate_series is working with Redshift now.

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

这将生成过去 30 天的日期

This will generate date for last 30 days

这篇关于generate_series() 方法在 Redshift 中失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！