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

refresh_materialized_views syntax diagram

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

Statements

Syntax

Data Types

Spatial Data

Constraints

Required privileges

Synopsis

Parameters

Examples

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

See also

​Required privileges

​Synopsis

​Parameters

​Examples

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

​See also

Required privileges

Synopsis

Parameters

Examples

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

See also