> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# REFRESH

Stored query results in [materialized view](/docs/stable/views.md#materialized-views) are not automatically updated to reflect the latest state of the table(s) they query. The `REFRESH` [statement](/docs/stable/sql-statements.md) updates the stored query results of a materialized view.

> [!NOTE]
>
> CockroachDB does not support materialized views that are refreshed on [transaction commit](/docs/stable/commit-transaction.md).

## Required privileges

The user must have the [`MAINTAIN` privilege](/docs/stable/security-reference/authorization#maintain) on the materialized view. The [owner](/docs/stable/alter-view.md) of the materialized view and users with [admin](/docs/stable/security-reference/authorization.md#admin-role) privileges implicitly have this privilege. To allow a non-owner to refresh a materialized view, run `GRANT MAINTAIN ON <materialized-view> TO <role_or_user>;`.

## Synopsis

![refresh\_materialized\_views syntax diagram](https://mintcdn.com/cockroachlabs/R40d76ku6ZWAJ59v/images/sql-diagrams/v26.3/refresh_materialized_views.svg?fit=max\&auto=format\&n=R40d76ku6ZWAJ59v\&q=85\&s=daad27e1c5d4164ccb8cf8332b1161d6)

## Parameters

| Parameter | Description |
| - | - |
| `opt_concurrently` | `CONCURRENTLY` (Default behavior) This keyword has no effect. It is present for PostgreSQL compatibility. All materialized views are refreshed concurrently with other jobs. |
| `view_name` | The name of the materialized view to refresh. |
| `opt_clear_data` | `WITH DATA` (Default behavior) Refresh the stored query results. `WITH NO DATA` Drop the query results of the materialized view from storage. |
| `AS OF SYSTEM TIME` | Use historical data when refreshing the view. This can reduce [contention](/docs/stable/performance-best-practices-overview.md#transaction-contention) by leveraging [follower reads](/docs/stable/follower-reads.md). The timestamp must be within the [garbage collection window](/docs/stable/configure-replication-zones.md#gc-ttlseconds). For more information, see [`AS OF SYSTEM TIME`](/docs/stable/as-of-system-time). |

## Examples

The following example uses the [sample `bank` database](/docs/stable/cockroach-workload#bank-workload), populated with some workload values.

Suppose that you create a materialized view on the `bank` table:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE MATERIALIZED VIEW overdrawn_accounts
  AS SELECT id, balance
  FROM bank
  WHERE balance < 0;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM overdrawn_accounts;
```

```
  id  | balance
------+----------
    1 |  -17643
    3 |   -5928
   13 |   -3700
...
(402 rows)
```

Now suppose that you update the `balance` values of the `bank` table:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> UPDATE bank SET balance = 0 WHERE balance < 0;
```

```
UPDATE 402
```

The changes can be seen in the table with a simple `SELECT` statement against the table:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT id, balance
FROM bank
WHERE balance < 0;
```

```
  id | balance
-----+----------
(0 rows)
```

Recall that materialized views do not automatically update their stored results. Selecting from `overdrawn_accounts` returns stored results, which are outdated:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM overdrawn_accounts;
```

```
  id  | balance
------+----------
    1 |  -17643
    3 |   -5928
   13 |   -3700
...
(402 rows)
```

To update the materialized view's results, use a [`REFRESH`](/docs/stable/refresh) statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> REFRESH MATERIALIZED VIEW overdrawn_accounts;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM overdrawn_accounts;
```

```
  id | balance
-----+----------
(0 rows)
```

### Refresh a materialized view with historical data using `AS OF SYSTEM TIME`

You can refresh a materialized view using historical data with the [`AS OF SYSTEM TIME`](/docs/stable/as-of-system-time#parameters) clause. This is useful for reducing [contention](/docs/stable/performance-best-practices-overview.md#transaction-contention) by performing a [follower read](/docs/stable/follower-reads.md) when refreshing the view.

> [!NOTE]
>
> Historical data is available only within the [garbage collection window](/docs/stable/configure-replication-zones.md#gc-ttlseconds).

Refresh a materialized view using [`follower_read_timestamp()`](/docs/stable/functions-and-operators#date-and-time-functions) to use the most recent data that is available for [follower reads](/docs/stable/follower-reads.md):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
REFRESH MATERIALIZED VIEW overdrawn_accounts
  AS OF SYSTEM TIME follower_read_timestamp();
```

You can also specify an explicit timestamp:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
REFRESH MATERIALIZED VIEW overdrawn_accounts
  AS OF SYSTEM TIME '-10s';
```

## See also

* [Materialized views](/docs/stable/views.md#materialized-views)
* [`CREATE VIEW`](/docs/stable/create-view)
* [`SHOW TABLES`](/docs/stable/show-tables)
* [`ALTER VIEW`](/docs/stable/alter-view)
* [`DROP VIEW`](/docs/stable/drop-view)
* [`AS OF SYSTEM TIME`](/docs/stable/as-of-system-time)
* [Follower Reads](/docs/stable/follower-reads.md)