Continuous aggregate for narrow table

Special Interest Groups IIoT
1

Hi,

I have a hyper narrow table with structure as “time | parameter_name” the values are stored in “parameter_name” column. When i try to create continuous aggregate for this table it is not getting created

Error message

ERROR: column “interval” does not exist
LINE 2: AS SELECT time_bucket(INTERVAL %s, "time…
^
The query is:

CREATE MATERIALIZED VIEW if not exists “power_consumption_agg_1” WITH (timescaledb.continuous)
AS SELECT time_bucket(INTERVAL %s, “time”) AS local_created_at, avg(power_consumption) AS power_consumption_avg FROM “power_cons_1” GROUP BY time_bucket(INTERVAL %s, “time”) WITH NO DATA;

It seems additional grouping column like “id” or “sensor_id” is required in the narrow table. Could you please help me to create continuous aggregate with above table structure, any input or workaround would be great help.

Thanks

Regards,
Arun

2

The continuous aggregate creation for narrow table works fine. The issue was i passed different time column which was not referenced in the source hypertable as dimension column. My apologies. Thanks