Create continues aggregate produce error

1

CREATE MATERIALIZED VIEW history_data_forex_1d
WITH (timescaledb.continuous) AS
SELECT
time_bucket(‘1 day’, time_1h AT TIME ZONE ‘America/New_York’ + interval ‘7 hour’) AS time_1d,
slug,
first(open, time_1h) AS open,
last(close, time_1h) AS close,
max(high) AS high,
min(low) AS low,
sum(volume) AS volume
FROM history_data_forex_1h
GROUP BY slug, time_1d
WITH NO DATA;

error = > ERROR: time bucket function must reference a hypertable dimension column

2

The error you’re encountering indicates that the time_bucket function used in the SELECT statement must reference a hypertable dimension column. In your case, the time_bucket function should be applied to a column that represents time in the hypertable.

To resolve the error, ensure that you replace the time_bucket function’s argument with a valid column representing time. Here’s an example of how you could modify your query:

CREATE MATERIALIZED VIEW history_data_forex_1d
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 day', time_1h AT TIME ZONE 'America/New_York' + interval '7 hour') AS time_1d,
  slug,
  first(open, time_1h) AS open,
  last(close, time_1h) AS close,
  max(high) AS high,
  min(low) AS low,
  sum(volume) AS volume
FROM history_data_forex_1h
GROUP BY slug, time_bucket('1 day', time_1h AT TIME ZONE 'America/New_York' + interval '7 hour')
WITH NO DATA;

In this modified query, the time_bucket function is also included in the GROUP BY clause to ensure it references a valid dimension column. Adjust the time_1h column in the time_bucket function according to your table structure to resolve the error.

3

The error message indicates that the time_bucket function used in the SELECT statement does not reference a hypertable dimension column. In TimescaleDB, when creating a continuous aggregate (materialized view) with the timescaledb.continuous parameter, the time bucketing function must be used on a hypertable dimension column.

The error seems to be arising from the use of time_1h in the time_bucket function. It appears that time_1h is not recognized as a hypertable dimension column.

To resolve this issue, you should check the schema of the history_data_forex_1h hypertable and make sure that the time_1h column is defined as a hypertable dimension column. If it’s not, you need to use the correct hypertable dimension column for time bucketing.

Here’s an example of how you can create a continuous aggregate with the correct time bucketing using a hypothetical column named timestamp as the hypertable dimension column:
– Assuming that ‘timestamp’ is the hypertable dimension column in history_data_forex_1h
– Replace ‘timestamp’ with the actual hypertable dimension column if different.

CREATE MATERIALIZED VIEW history_data_forex_1d
WITH (timescaledb.continuous) AS
SELECT
time_bucket(‘1 day’, “timestamp” AT TIME ZONE ‘America/New_York’ + interval ‘7 hour’) AS time_1d,
slug,
first(open, “timestamp”) AS open,
last(close, “timestamp”) AS close,
max(high) AS high,
min(low) AS low,
sum(volume) AS volume
FROM history_data_forex_1h
GROUP BY slug, time_1d
WITH NO DATA;
if the above anaswer helps you i would like to add one thing. there is centre in atlanta women’s rehab center Atlanta,do check this out.

1 Like