redshift-如何插入表格生成的时间序列 [英] redshift - how to insert into table generated time series

问题描述

我正在尝试在Redshift中生成时间序列并将其插入表中，但是没有运气.到目前为止，我已经尝试过:

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;

出现以下错误

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 ?

推荐答案

问题在于generate_series()可以在Leader节点上运行，但不能在计算节点上运行.

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)

但是，不可能在FROM中使用该语句，因为这将涉及计算节点.

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

解决方案:在外部创建信息表，然后将结果加载到date表中，或者直接使用generate_series()生成所需的值，保存结果并将其导入到date表格.

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.

这篇关于redshift-如何插入表格生成的时间序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！