CREATE MATERIALIZED VIEW statement is used to create continuous \
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 ...]
The continuous aggregate view is automatically refreshed unless
WITH NO DATA
is given. This setting defaults to
WITH DATA. For more information, see
Continuous aggregates have some limitations of what types of queries they can
support, described in more length below. For example, the
FROM clause must
provide only one hypertable, and joins, CTEs, views or subqueries are not
GROUP BY clause must include a time bucket on the hypertable
time column, and all aggregates must be parallelizable.
Some important things to remember when constructing your
FROMclause of the
SELECTquery. You cannot include more hypertables, joins, tables, views, or subqueries.
SELECTquery might not have row-level-security policies enabled.
GROUP BYclause must include a
time_bucketexpression that uses the time dimension of the hypertable. For more information, see the
time_bucket_gapfillin continuous aggregates, but you can run them in a
SELECTquery from the continuous aggregate view.
FILTERclauses are not supported.
HAVINGclauses must be immutable.
The settings for continuous aggregates are in the informational views.
|TEXT||Name (optionally schema-qualified) of continuous aggregate view to create|
|TEXT||Optional list of names to be used for columns of the view. If not given, the column names are calculated from the query|
|TEXT||Specifies options for the continuous aggregate view|
WITH clause options:
WITH clause options:
|BOOLEAN||Return only materialized data when querying the continuous aggregate view.|
|BOOLEAN||Create indexes on the materialization table for the group by columns specified by the |
For more information, see the real-time aggregates section.
Create a daily continuous aggregate view:
CREATE MATERIALIZED VIEW continuous_aggregate_daily( 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 a thirty day continuous aggregate on top of the same raw hypertable:
CREATE MATERIALIZED VIEW continuous_aggregate_thirty_day( 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);
Add an hourly continuous aggregate on top of the same raw hypertable:
CREATE MATERIALIZED VIEW continuous_aggregate_hourly( 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!