Open gma2th opened 4 years ago
Currently only one hypertable can be used in FROM clause in cagg definition. If I don't mistake, any query with CTE confirming this requirement can be easily converted to a query without CTE. @gma2th Do you have any specific reason why you need such simple CTEs? It seems there is no point to have them with the current limitation on caggs.
I query only one hypertable. I can do it without a cte, but using a cte avoid to repeat myself and simplify the query as I make several operations between the columns and their aggregated value.
@gma2th Thank you for clarifying! Can you provide an example(s) where you demonstrate that CTE with single hypertable helps to define simple query? E.g., the same cagg definition with CTE and without CTE where it is easy to see your point. The examples in OP don't really support well your request.
Here's my current issue as an example for a CTE:
I want to create 7 very similar continuous aggregate which differ only in their categories from 1 to 7.
... WHERE id IN (SELECT id FROM sensors_to_categories WHERE cat = 7)
Table sensors_to_categories
holds many thousand entries, assigning each sensor to either of the 7 categories.
I would not store the category id in the hypertable along with every single sensor value, so there is no version without CTE.
Is there a different way to achieve the same goal? Otherwise original sensor data is deleted after 30 days via retention_policy.
Given that CTEs are part of ANSI SQL, the lack of support for it should be mentioned as a clear warning on the documentation pages for continuous aggregates. Even the simple CTE in OP shows how CTEs can help you to properly make your SQL clearer and more understandable. Why you should care about CTEs
+1, Would also be very useful.
Relevant system information:
postgres --version
): 12.2\dx
inpsql
): 1.7.1Describe the bug Using a cte in a continuous aggregate will raise the following error:
To Reproduce
Expected behavior Is there a reason to not support cte in continuous aggregate? Is it planned to support them in the future?