REFRESH

Stored query results in are not automatically updated to reflect the latest state of the table(s) they query. The REFRESH updates the stored query results of a materialized view.

CockroachDB does not support materialized views that are refreshed on .

Required privileges

The user must have the on the materialized view. The of the materialized view and users with 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

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 by leveraging . The timestamp must be within the . For more information, see .

Examples

The following example uses the , populated with some workload values.

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

> CREATE MATERIALIZED VIEW overdrawn_accounts
  AS SELECT id, balance
  FROM bank
  WHERE balance < 0;

> 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:

> 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:

> 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:

> SELECT * FROM overdrawn_accounts;

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

To update the materialized view’s results, use a statement:

> REFRESH MATERIALIZED VIEW overdrawn_accounts;

> 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 clause. This is useful for reducing by performing a when refreshing the view.

Historical data is available only within the .

Refresh a materialized view using to use the most recent data that is available for :

REFRESH MATERIALIZED VIEW overdrawn_accounts
  AS OF SYSTEM TIME follower_read_timestamp();

You can also specify an explicit timestamp:

REFRESH MATERIALIZED VIEW overdrawn_accounts
  AS OF SYSTEM TIME '-10s';

See also