Hello! For my use case, I’m looking to view the data from one hypertable in a different format in real time (e.g. convert between wide, narrow, and medium table formats or modify column names). I think this is possible with materialized views using a trigger for table operations, but I wanted to check if there is a better Timescale way to do this. For instance, can I use real time aggregates without actually aggregating the data?
I think the continuous aggregates will force you to use a time_bucket and maybe you can configure a millisecond bucket as interval to get unique values. It will depend on how much precision do you need.
But you can also build the same with background actions with a bit more effort or use triggers.
@noctarius is working on a very good alternative that is the event streamer which allow you to configure what event you want to receive being able to get both hypertable and caggs data.
Thanks Jônatas, I will take a look at sub-second continuous aggregates. As a follow up, if I have columns with data types that can’t be aggregated (e.g. large binary, text, etc), can I still use aggregates and somehow avoid aggregating those columns?
Yes! Sorry for the late reply, was on vacation. You can use and just avoid such columns.
You can probably use like string_agg for text but not sure about blobs and probably it would not make sense to keep adding such huge data in the caggs as it will cost the double of the space.