CREATE MATERIALIZED VIEW (Continuous Aggregate)

Create a continuous aggregate on a hypertable or another continuous aggregate

Since 2.22.0

You use the CREATE MATERIALIZED VIEW statement to create s. To learn more, see the 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 view defaults to WITH DATA. This means that when the view is created, it refreshes using all the current data in the underlying or . 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.

s have some limitations of what types of queries they can support. For more information, see the s section.

v2.17.1 and greater dramatically decrease the amount of data written on a in the presence of a small number of changes, reduce the i/o cost of refreshing a , and generate fewer Write-Ahead Logs (WAL), set thetimescaledb.enable_merge_on_cagg_refresh configuration parameter to TRUE. This enables refresh to use merge instead of deleting old materialized data and re-inserting.

For more settings for s, see timescaledb_information.continuous_aggregates.

Samples

Create a daily 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 on top of the same raw :

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 on top of the same raw :

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

Arguments

Name	Type	Default	Required	Description
<view_name>	TEXT	-	✔	Name (optionally schema-qualified) of 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 view
<select_query>	TEXT	-	✔	A SELECT query that uses the specified syntax

Required WITH clause options:

Name	Type	Default	Required	Description
timescaledb.continuous	BOOLEAN	-	✔	If timescaledb.continuous is not specified, this is a regular materialized view

Optional WITH clause options:

Name	Type	Default	Required	Description
timescaledb.chunk_interval	INTERVAL	10x the original	-	Set the interval. The default value is 10x the original .
timescaledb.create_group_indexes	BOOLEAN	TRUE	-	Create indexes on the for columns in its GROUP BY clause. Indexes are in the form (<GROUP_BY_COLUMN>, time_bucket)
timescaledb.finalized	BOOLEAN	TRUE	-	In 2.7 and above, use the new version of s, which stores finalized results for aggregate functions. Supports all aggregate functions, including ones that use FILTER, ORDER BY, and DISTINCT clauses.
timescaledb.materialized_only	BOOLEAN	TRUE	-	Return only materialized data when querying the view
timescaledb.invalidate_using	TEXT	trigger	-	Set to wal to read changes from the WAL using logical decoding, then update the materialization invalidations for s using this information. This reduces the I/O and CPU needed to manage the invalidation log. Set to trigger to collect invalidations whenever there are inserts, updates, or deletes to a . This default behaviour uses more resources than wal.

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