How to create multiple continuous aggregates over one table in TimescaleDB?

user_3467894 0
Viewed 2438

I have this series table with its hypertable. I would like to have different continuous aggregates over data in this table.

CREATE TABLE series (
    time TIMESTAMPTZ PRIMARY KEY,
    value INTEGER
);

SELECT create_hypertable('series', 'time');

CREATE VIEW mat_view1
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 day', time) AS day,
AVG(value)
FROM series
GROUP BY day;

CREATE VIEW mat_view2
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 week', time) AS week,
COUNT(value)
FROM series
GROUP BY week;

But it seems it is not possible in PostgreSQL 11 - this is what I get when I run the query above:

ERROR:  hypertable already has a continuous aggregate
RECOMMENDATION:  hypertables currently only support a single continuous aggregate. Drop the other continuous aggreagate to add a new one.

It is not even possible to create a different hypertable over same table.

ERROR:  hypertable already has a continuous aggregate
RECOMMENDATION:  hypertables currently only support a single continuous aggregate. Drop the other continuous aggreagate to add a new one.

Is it possible to workaround this limitation? Or should I use another approach (e.g. duplicate series table for each continuous aggregate :-x)?

postgresql-11 timescaledb sql
1 Answers

As of TimescaleDB 1.4.0 this is supported, see: https://github.com/timescale/timescaledb/blob/master/CHANGELOG.md#140-2019-07-18

Previously this was not supported in Timescale, however you could do the following: you create 1 continuous aggregate which contains all the details you may need, and you create a regular view for your second usecase.

CREATE VIEW mat_view1
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 day', time) AS day,
AVG(value) AS avg,
COUNT(value) AS count
FROM series
GROUP BY day;

CREATE VIEW view2 AS
SELECT time_bucket('1 week', day) AS week,
SUM(count) AS count
FROM mat_view1
GROUP BY week;
user_24596560
Related