REFRESH

On this page

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.

Required privileges

The user must be the owner of the materialized view.

Syntax

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

Parameters

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:

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

> REFRESH MATERIALIZED VIEW overdrawn_accounts;

> SELECT * FROM overdrawn_accounts;

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

See also

• Materialized views
• CREATE VIEW
• SHOW TABLES
• ALTER VIEW
• DROP VIEW