CREATE MATERIALIZED VIEW (Continuous Aggregate)

Create a continuous aggregate on a hypertable or another continuous aggregate

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.

In v2.17.0 and greater (with 15+), you can 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) by enabling the timescaledb.enable_merge_on_cagg_refresh GUC parameter. This enables refresh to use MERGE instead of deleting old materialized data and re-inserting. This parameter only works for finalized s that don’t have compression enabled. It is disabled by default.

To enable this parameter for your session:

SET timescaledb.enable_merge_on_cagg_refresh = ON;

To enable it at the database level:

ALTER DATABASE your_database SET timescaledb.enable_merge_on_cagg_refresh = ON;

For more information about GUC parameters, see the configuration documentation.

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

WITH clause options:

Name	Type	Default	Required	Description
timescaledb.continuous	BOOLEAN	-	✔	If timescaledb.continuous is not specified, this is a regular materialized view
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.materialized_only	BOOLEAN	TRUE	-	Return only materialized data when querying the view

Returns

For standard CREATE MATERIALIZED VIEW return behavior, see the PostgreSQL CREATE MATERIALIZED VIEW documentation.

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