---
title: CREATE MATERIALIZED VIEW (continuous aggregate) | Tiger Data Docs
description: Create a continuous aggregate on a hypertable or another continuous aggregate
---

You use the `CREATE MATERIALIZED VIEW` statement to create continuous aggregates. To learn more, see the [continuous aggregate how-to guides](/docs/build/continuous-aggregates/create-a-continuous-aggregate/index.md).

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 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`](/docs/reference/timescaledb/continuous-aggregates/refresh_continuous_aggregate/index.md).

Continuous aggregates have some limitations of what types of queries they can support. For more information, see the [continuous aggregates section](/docs/build/continuous-aggregates/create-a-continuous-aggregate/index.md).

In TimescaleDB v2.17.0 and greater (with PostgreSQL 15+), you can 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) by enabling the `timescaledb.enable_merge_on_cagg_refresh` [GUC parameter](/docs/reference/timescaledb/configuration/gucs/index.md). This enables continuous aggregate refresh to use `MERGE` instead of deleting old materialized data and re-inserting. This parameter only works for finalized continuous aggregates that don’t have columnstore 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](/docs/reference/timescaledb/configuration/gucs/index.md).

For more settings for continuous aggregates, see [timescaledb\_information.continuous\_aggregates](/docs/reference/timescaledb/informational-views/continuous_aggregates/index.md).

## Samples

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

## Arguments

| Name             | Type | Default | Required | Description                                                                                                             |
| ---------------- | ---- | ------- | -------- | ----------------------------------------------------------------------------------------------------------------------- |
| `<view_name>`    | TEXT | -       | ✔        | Name (optionally schema-qualified) of continuous aggregate 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 continuous aggregate 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 PostgreSQL materialized view                                                |
| `timescaledb.chunk_interval`       | INTERVAL | 10x the original hypertable | -        | Set the chunk interval. The default value is 10x the original hypertable.                                                                   |
| `timescaledb.create_group_indexes` | BOOLEAN  | `TRUE`                      | -        | Create indexes on the continuous aggregate 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 continuous aggregate view                                                                   |

## Returns

For standard `CREATE MATERIALIZED VIEW` return behavior, see the [PostgreSQL CREATE MATERIALIZED VIEW documentation](https://www.postgresql.org/docs/current/sql-creatematerializedview.html).

For more information, see the [real-time aggregates](/docs/learn/continuous-aggregates/real-time-aggregates/index.md) section.