CREATE MATERIALIZED VIEW (Continuous Aggregate)

The 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 ...]

The continuous aggregate view is automatically refreshed unless WITH NO DATA is given. This setting defaults to WITH DATA. 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, and joins, CTEs, views or subqueries are not supported. The 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 SELECT query:

  • Only a single hypertable can be specified in the FROM clause of the SELECT query. You cannot include more hypertables, joins, tables, views, or subqueries.
  • The hypertable used in the SELECT query might not have row-level-security policies enabled.
  • The GROUP BY clause must include a time_bucket expression that uses the time dimension of the hypertable. For more information, see the time_bucket section.
  • You cannot use time_bucket_gapfill in continuous aggregates, but you can run them in a SELECT query from the continuous aggregate view.
  • You can usually use aggregates that are parallelized by PostgreSQL in the view definition, i ncluding most aggregates distributed by PostgreSQL. However, the ORDER BY, DISTINCT and FILTER clauses are not supported.
  • All functions and their arguments included in SELECT, GROUP BY and HAVING 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.

Parameters

NameTypeDescription
<view_name>TEXTName (optionally schema-qualified) of continuous aggregate view to create
<column_name>TEXTOptional list of names to be used for columns of the view. If not given, the column names are calculated from the query
WITH clauseTEXTSpecifies options for the continuous aggregate view
<select_query>TEXTA SELECT query that uses the specified syntax

Required WITH clause options:

NameTypeDescription
timescaledb.continuousBOOLEANIf timescaledb.continuous is not specified, this is a regular PostgresSQL materialized view

Optional WITH clause options:

NameTypeDescription
timescaledb.materialized_onlyBOOLEANReturn only materialized data when querying the continuous aggregate view.
timescaledb.create_group_indexesBOOLEANCreate indexes on the materialization table for the group by columns specified by the GROUP BY clause of the SELECT query

For more information, see the real-time aggregates section.

Sample use

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!

Related Content