CREATE MATERIALIZED VIEW (Continuous Aggregate)

The CREATE MATERIALIZED VIEW statement is used to create continuous aggregates. To learn more, see the continuous aggregate 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]

Copy

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

Copy

The continuous aggregate view defaults to WITH DATA. This means that when the view is created, it refreshes using all the current data in the underlying hypertable or continuous aggregate. 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.

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

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

For more settings for continuous aggregates, see timescaledb_information.continuous_aggregates.

Required WITH clause options:

Optional WITH clause options:

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

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)

Copy

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

Copy

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

Copy