CREATE MATERIALIZED VIEW statement is used to create continuous aggregates.
The syntax is:
CREATE MATERIALIZED VIEW <view_name> [ ( column_name [, ...] ) ] WITH ( timescaledb.continuous [, timescaledb.<option> = <value> ] ) AS <select_query> [WITH [NO] DATA]
<select_query> is of the form :
SELECT <grouping_exprs>, <aggregate_functions> FROM <hypertable> [WHERE ... ] GROUP BY time_bucket( <const_value>, <partition_col_of_hypertable> ), [ optional grouping exprs>] [HAVING ...]
Note that continuous aggregates have some limitations of what types of
queries they can support, described in more length below. For example,
FROM clause must provide only one hypertable, i.e., no joins, CTEs, views or
subqueries are supported. The
GROUP BY clause must include a time bucket on
the hypertable's time column, and all aggregates must be parallelizable.
|TEXT||Name (optionally schema-qualified) of continuous aggregate view to be created.|
|TEXT||Optional list of names to be used for columns of the view. If not given, the column names are deduced from the query.|
|TEXT||This clause specifies options for the continuous aggregate view.|
|If timescaledb.continuous is not specified, then this is a regular PostgresSQL materialized view.|
|Return only materialized data when querying the continuous aggregate view. See more in section on [real-time aggregates][real-time-aggregates].||false|
|Create indexes on the materialization table for the group by columns (specified by the ||Indexes are created by default for every group by expression + time_bucket expression pair.|
WITH NO DATAis given (
WITH DATAis the default).
SELECTquery should be of the form specified in the syntax above, which is discussed in the following items.
FROMclause of the
SELECTquery. This means that including more hypertables, joins, tables, views, subqueries is not supported.
SELECTmay not have row-level-security policies enabled.
GROUP BYclause must include a time_bucket expression. The
time_bucketexpression must use the time dimension column of the hypertable.
time_bucket_gapfillis not allowed in continuous aggs, but may be run in a
SELECTfrom the continuous aggregate view.
FILTERclauses are not permitted.
HAVINGclauses must be immutable.
Create a continuous aggregate view.
CREATE MATERIALIZED VIEW continuous_aggregate_view( timec, minl, sumt, sumh ) WITH (timescaledb.continuous) AS SELECT time_bucket('1day', timec), min(location), sum(temperature), sum(humidity) FROM conditions GROUP BY time_bucket('1day', timec)
Add additional continuous aggregates on top of the same raw hypertable.
CREATE MATERIALIZED VIEW continuous_aggregate_view( timec, minl, sumt, sumh ) WITH (timescaledb.continuous) AS SELECT time_bucket('30day', timec), min(location), sum(temperature), sum(humidity) FROM conditions GROUP BY time_bucket('30day', timec);
CREATE MATERIALIZED VIEW continuous_aggregate_view( timec, minl, sumt, sumh ) WITH (timescaledb.continuous) AS SELECT time_bucket('1h', timec), min(location), sum(temperature), sum(humidity) FROM conditions GROUP BY time_bucket('1h', timec);
Found an issue on this page?Report an issue!