Continuous aggregate multiple joins give "Subqueries are not supported in from clause" error

TimescaleDB and PostgreSQL
1

Hi, I am trying to create a materialized view for a continuous aggregate where I have one hypertable I am trying to join with two normal tables. I am using Postgres 16 on RedHat Linux with Timescale 2.16.0-dev

This is the query (column names generalized):

select time_bucket(interval '1 day', hypertable.time_column) as bucket, hypertable.reftable2id, sum(hypertable.metric_value)
from hypertable join ref_table1 on ref_table1.id = hypertable.reftable1id 
join ref_table2 on ref_table2.id = hypertable.reftable2id
group by bucket, hypertable.reftable2id;

What am I doing wrong?

2

Hi @kunjmehta, avoid materializing joined data and just make the references and use the join over a regular view.

So, your next step is just create a view with the joins and use the view over it. So, you avoid duplicating and generating all this dependencies in the view level.

Why?

Because it would be very hard to track joined table changes on aggregated view, slowing down the performance of the aggregation.

1 Like