CREATE MATERIALIZED VIEW (Continuous Aggregate)

Community

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, the 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.

Parameters

Required WITH clause options

Optional WITH clause options

Notes

• The view will be automatically refreshed (as outlined under refresh_continuous_aggregate) unless WITH NO DATA is given (WITH DATA is the default).
• The SELECT query should be of the form specified in the syntax above, which is discussed in the following items.
• Only a single hypertable can be specified in the FROM clause of the SELECT query. This means that including more hypertables, joins, tables, views, subqueries is not supported.
• The hypertable used in the SELECT may not have row-level-security policies enabled.
• The GROUP BY clause must include a time_bucket expression. The time_bucket expression must use the time dimension column of the hypertable.
• time_bucket_gapfill is not allowed in continuous aggs, but may be run in a SELECT from the continuous aggregate view.
• In general, aggregates which can be parallelized by PostgreSQL are allowed in the view definition, this includes most aggregates distributed with PostgreSQL. Aggregates with ORDER BY, DISTINCT and FILTER clauses are not permitted.
• All functions and their arguments included in SELECT, GROUP BY and HAVING clauses must be immutable.
• The view is not allowed to be a security barrier view.
• Window functions cannot be used in conjunction with continuous aggregates.

tip

You can find the settings for continuous aggregates and statistics in timescaledb_information views.

Sample Usage

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);