CREATE MATERIALIZED VIEW (Continuous Aggregate)
CommunityCommunity functions are available under Timescale Community Edition. Click to learn more.The CREATE MATERIALIZED VIEW
statement is used to create continuous
aggregates. To learn more, see the continuous aggregate how-to guides.
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 or another continuous aggregate>[WHERE ... ]GROUP BY time_bucket( <const_value>, <partition_col_of_hypertable> ),[ optional grouping exprs>][HAVING ...]
The continuous aggregate view defaults to WITH DATA
. This means that when the
view is created, it refreshes using all the current data in the underlying
hypertable or continuous aggregate. This occurs once when the view is created. If you want the view to
be refreshed regularly, you can use a refresh policy. If you do not want the
view to update when it is first created, use the WITH NO DATA
parameter. For more information, see
refresh_continuous_aggregate
.
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 or underlying continuous aggregate, and joins, CTEs, or subqueries are not
supported. The GROUP BY
clause must include a time bucket on the underlying
time column, and all aggregates must be parallelizable.
Some important things to remember when constructing your SELECT
query:
- Only a single hypertable or continuous aggregate can be specified in the
FROM
clause of theSELECT
query. You cannot include more hypertables, joins, tables, views, or subqueries. - The source hypertable or continuous aggregate used in the
SELECT
query must not have row-level-security policies enabled. - The
GROUP BY
clause must include atime_bucket
expression that uses the time dimension of the hypertable. When creating a continuous aggregate on top of another continuous aggregate,time_bucket
must use the time-bucketing column of the underlying continuous aggregate. For more information, see thetime_bucket
section. - You cannot use
time_bucket_gapfill
in continuous aggregates, but you can run them in aSELECT
query from the continuous aggregate view. - You can usually use aggregates that are
parallelized by PostgreSQL in the view definition,
including most aggregates distributed by PostgreSQL. However, the
ORDER BY
,DISTINCT
andFILTER
clauses are not supported. - All functions and their arguments included in
SELECT
,GROUP BY
andHAVING
clauses must be immutable. - The view cannot be a security barrier view.
- You cannot use Window functions with continuous aggregates.
The settings for continuous aggregates are in the informational views.
Name | Type | Description |
---|---|---|
<view_name> | TEXT | Name (optionally schema-qualified) of continuous aggregate view to create |
<column_name> | TEXT | Optional list of names to be used for columns of the view. If not given, the column names are calculated from the query |
WITH clause | TEXT | Specifies options for the continuous aggregate view |
<select_query> | TEXT | A SELECT query that uses the specified syntax |
Required WITH
clause options:
Name | Type | Description |
---|---|---|
timescaledb.continuous | BOOLEAN | If timescaledb.continuous is not specified, this is a regular PostgresSQL materialized view |
Optional WITH
clause options:
Name | Type | Description | Default value |
---|---|---|---|
timescaledb.materialized_only | BOOLEAN | Return only materialized data when querying the continuous aggregate view | FALSE |
timescaledb.create_group_indexes | BOOLEAN | Create indexes on the continuous aggregate for columns in its GROUP BY clause. Indexes are in the form (<GROUP_BY_COLUMN>, time_bucket) | TRUE |
timescaledb.finalized | BOOLEAN | In TimescaleDB 2.7 and above, use the new version of continuous aggregates, which stores finalized results for aggregate functions. Supports all aggregate functions, including ones that use FILTER , ORDER BY , and DISTINCT clauses. | TRUE |
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) ASSELECT time_bucket('1day', timec), min(location), sum(temperature), sum(humidity)FROM conditionsGROUP 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) ASSELECT time_bucket('30day', timec), min(location), sum(temperature), sum(humidity)FROM conditionsGROUP 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) ASSELECT time_bucket('1h', timec), min(location), sum(temperature), sum(humidity)FROM conditionsGROUP BY time_bucket('1h', timec);
Found an issue on this page?
Report an issue!Keywords