Timescaledb: Create continuous aggregate from multiple tables

[suiluj]

Hello everybody,

I have a question regarding time_bucket and continuous aggregation for multiple tables.
I want to use time_bucket() to match timestamps across different time series. Exactly like in this video at minute 38:51: https://youtu.be/lkqmfsWx6GM?t=2331

How should i create a query for a continuous aggregation to get metric_1 of table_1 and metric_2 of table_2 in one continious aggregation view.
In our case there could be different numbers of rows. Some will match and some rows will contain null values when "joined".
So i am planning to use time_bucket_gapfill() when i query the continuous aggregation view later.
Is there an example on how to create a continuous aggregation of multiple tables? In our case there has to be a "join" on time and a device_id (vin).

As an multi step solution i tried creating continuous aggregates for every table.
ca_one and ca_two are both continuous aggregates with 2 second time buckets.

The following query is successful and return exactly what i neeed:

  ca_one.time_2s,
  ca_one.vin,
  column_first_table,
  column_second_table,
FROM
  ca_one
  LEFT JOIN ca_two ON ca_one.time_2s = ca_two.time_2s
  AND ca_one.vin = ca_two.vin
GROUP BY
  ca_one.time_2s,
  ca_one.vin,
  column_first_table,
  column_second_table;

But when i try to use it in a continuous aggregate i get the error:

ERROR:  only 1 hypertable is permitted in SELECT query for continuous aggregate

I don't know how to get my combined results time efficiently.
Does someone have a solution for this kind of (i think general) use case? Perhaps i am missing something obvious.

Regards
Julius

[gayyappan]

You would create 2 cont agg. views and join the views when querying. Cont aggs can only be defined for a hypertable.

[suiluj]

yes this is of course bad for performance because it will join a lot of data in a live query.

a different approach would be to use wider tables with many columns. this can have impact on performance too. and there will be a lot of null values in the table. (i read it is not too bad to have null values but it is not great either.

Perhaps someone has a solution for this general problem?

[gayyappan]

This is a known limitation. @bboule

[swiffer]

is it supported to join "regular" tables when defining a continous aggegrate? I'm currently experiencing the same error message in that case.

[k-rus]

@swiffer It is not supported for regular table. From clause can contain just single hypertable.

[eloyekunle]

This will be a really useful feature to have.

[LionelR]

Is there some news for this feature?

[BenM-Mycelium]

This would be extremely helpful but I presume this is more of a limitation on psql in general rather than a timescale specific issue. Would be a game changer though, definitely.