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

Note:

CockroachDB does not support materialized views that are refreshed on transaction commit.

Required privileges

The user must be the owner of the materialized view.

Syntax

REFRESH MATERIALIZED VIEW [CONCURRENTLY] view_name [WITH [NO] DATA]

Parameters

Parameter Description
CONCURRENTLY (Default behavior) This keyword is a no-op, added for PostgreSQL compatibility. All materialized views are refreshed concurrently with other jobs.
view_name The name of the materialized view to refresh.
WITH NO DATA Drop the query results of the materialized view from storage.
WITH DATA (Default behavior) Refresh the stored query results.

Example

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

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

copy
icon/buttons/copy
> CREATE MATERIALIZED VIEW overdrawn_accounts
  AS SELECT id, balance
  FROM bank
  WHERE balance < 0;
copy
icon/buttons/copy
> 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:

copy
icon/buttons/copy
> 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:

copy
icon/buttons/copy
> 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:

copy
icon/buttons/copy
> SELECT * FROM overdrawn_accounts;
  id  | balance
------+----------
    1 |  -17643
    3 |   -5928
   13 |   -3700
...
(402 rows)

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

copy
icon/buttons/copy
> REFRESH MATERIALIZED VIEW overdrawn_accounts;
copy
icon/buttons/copy
> SELECT * FROM overdrawn_accounts;
  id | balance
-----+----------
(0 rows)

See also



Yes No