> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# CREATE MATERIALIZED VIEW (Continuous Aggregate)

> Create a continuous aggregate on a hypertable or another continuous aggregate

export const CHUNK = 'chunk';

export const PG = 'Postgres';

export const TIMESCALE_DB = 'TimescaleDB';

export const HYPERTABLE = 'hypertable';

export const CAGG_CAP = 'Continuous aggregate';

export const CAGG = 'continuous aggregate';

You use the `CREATE MATERIALIZED VIEW` statement to create {CAGG}s. To learn more, see the
[{CAGG} how-to guides][cagg-how-tos].

The syntax is:

```sql theme={"dark"}
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:

```sql theme={"dark"}
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 {CAGG} 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 {CAGG}. 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`][refresh-cagg].

{CAGG_CAP}s have some limitations of what types of queries they can
support. For more information, see the
[{CAGG}s section][cagg-how-tos].

In {TIMESCALE_DB} v2.17.0 and greater (with {PG} 15+), you can dramatically decrease the amount
of data written on a {CAGG} in the presence of a small number of changes,
reduce the I/O cost of refreshing a {CAGG}, and generate fewer Write-Ahead
Logs (WAL) by enabling the `timescaledb.enable_merge_on_cagg_refresh`
[GUC parameter][gucs]. This enables {CAGG}
refresh to use `MERGE` instead of deleting old materialized data and re-inserting.
This parameter only works for finalized {CAGG}s
that don't have compression enabled. It is disabled by default.

To enable this parameter for your session:

```sql theme={"dark"}
SET timescaledb.enable_merge_on_cagg_refresh = ON;
```

To enable it at the database level:

```sql theme={"dark"}
ALTER DATABASE your_database SET timescaledb.enable_merge_on_cagg_refresh = ON;
```

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

For more settings for {CAGG}s, see [timescaledb\_information.continuous\_aggregates][info-views].

## Samples

Create a daily {CAGG} view:

```sql theme={"dark"}
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 {CAGG} on top of the same raw {HYPERTABLE}:

```sql theme={"dark"}
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 {CAGG} on top of the same raw {HYPERTABLE}:

```sql theme={"dark"}
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 {CAGG} 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 {CAGG} 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 {PG} 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 {CAGG} 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 {CAGG} view                                                                   |

## Returns

For standard `CREATE MATERIALIZED VIEW` return behavior, see the [PostgreSQL CREATE MATERIALIZED VIEW documentation][postgres-create-matview].

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

[cagg-how-tos]: /use-timescale/latest/continuous-aggregates/

[gucs]: /api-reference/timescaledb/configuration/gucs

[info-views]: /api-reference/timescaledb/informational-views/continuous_aggregates

[postgres-create-matview]: https://www.postgresql.org/docs/current/sql-creatematerializedview.html

[real-time-aggregates]: /use-timescale/latest/continuous-aggregates/real-time-aggregates/

[refresh-cagg]: /api-reference/timescaledb/continuous-aggregates/refresh_continuous_aggregate