CREATE MATERIALIZED VIEW (Continuous Aggregate)

Community

CREATE MATERIALIZED VIEW statement is used to create continuous aggregates.

The syntax is:

  1. CREATE MATERIALIZED VIEW <view_name> [ ( column_name [, ...] ) ]
  2. WITH ( timescaledb.continuous [, timescaledb.<option> = <value> ] )
  3. AS
  4. <select_query>
  5. [WITH [NO] DATA]

<select_query> is of the form :

  1. SELECT <grouping_exprs>, <aggregate_functions>
  2. FROM <hypertable>
  3. [WHERE ... ]
  4. GROUP BY time_bucket( <const_value>, <partition_col_of_hypertable> ),
  5. [ optional grouping exprs>]
  6. [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

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

Required WITH clause options

Name
timescaledb.continuous
DescriptionTypeDefault
If timescaledb.continuous is not specified, then this is a regular PostgresSQL materialized view.BOOLEAN

Optional WITH clause options

Name
timescaledb.materialized_only
DescriptionTypeDefault
Return only materialized data when querying the continuous aggregate view. See more in section on [real-time aggregates][real-time-aggregates].BOOLEANfalse
timescaledb.create_group_indexes
DescriptionTypeDefault
Create indexes on the materialization table for the group by columns (specified by the GROUP BY clause of the SELECT query).BOOLEANIndexes are created by default for every group by expression + time_bucket expression pair.

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.

  1. CREATE MATERIALIZED VIEW continuous_aggregate_view( timec, minl, sumt, sumh )
  2. WITH (timescaledb.continuous) AS
  3. SELECT time_bucket('1day', timec), min(location), sum(temperature), sum(humidity)
  4. FROM conditions
  5. GROUP BY time_bucket('1day', timec)

Add additional continuous aggregates on top of the same raw hypertable.

  1. CREATE MATERIALIZED VIEW continuous_aggregate_view( timec, minl, sumt, sumh )
  2. WITH (timescaledb.continuous) AS
  3. SELECT time_bucket('30day', timec), min(location), sum(temperature), sum(humidity)
  4. FROM conditions
  5. GROUP BY time_bucket('30day', timec);
  1. CREATE MATERIALIZED VIEW continuous_aggregate_view( timec, minl, sumt, sumh )
  2. WITH (timescaledb.continuous) AS
  3. SELECT time_bucket('1h', timec), min(location), sum(temperature), sum(humidity)
  4. FROM conditions
  5. GROUP BY time_bucket('1h', timec);