Hi all,
is it possible to ensure unique data in the continuous aggreate? When I create a cagg as follows:
create materialized view gauges_1d
with (timescaledb.continuous) as
select time_bucket(interval '1d', time) as time, variable,
min(value) as min,
avg(value) as avg,
max(value) as max
from gauges group by 1, 2
The DDL assumes that the group by clause leads to unique (time, variable) rows in the resulting view.
But with cagg, I can insert historical data from $legacy_system directly, but unfortunately I can insert data with any time, not only time_bucket rounded, and even with the same (time, variable) as existing rows. Is it possible to forbid this in the DDL? Something like creating unique index or constraint (time, variable) on the cagg?
One of the main benefits of using SQL instead of a custom protocol and/or engine is that with SQL DDL it is possible to describe the data constraints declaratively, so that a rogue (or rather malfunctioning) client cannot break the inherent data assumptions.
Thanks!
-Yenya