REFRESH

On this page

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 or have admin privileges.

Synopsis

Parameters

Use historical data when refreshing the view. The timestamp must be within the garbage collection window. This can reduce contention by leveraging follower reads. For more information, see AS OF SYSTEM TIME.

Examples

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)

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

New in v25.2: You can refresh a materialized view using historical data with the AS OF SYSTEM TIME clause. This is useful for reducing contention by performing a follower read when refreshing the view.

Refresh a materialized view using follower_read_timestamp() to use the most recent data that is available for follower reads:

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

• Materialized views
• CREATE VIEW
• SHOW TABLES
• ALTER VIEW
• DROP VIEW
• AS OF SYSTEM TIME
• Follower Reads