Updated on 2025-09-18 GMT+08:00

REFRESH MATERIALIZED VIEW

Function

REFRESH MATERIALIZED VIEW refreshes materialized views. This syntax is supported only by clusters of version 8.2.1.220 or later.

The refresh mode is specified by the REFRESH parameter in the CREATE MATERIALIZED VIEW syntax. Currently, full refresh and scheduled refresh are supported.

Precautions

The refresh operation blocks the DML operations on the base table.

Syntax

1
2
REFRESH MATERIALIZED VIEW
{[schema.]materialized_view_name} [ [DEFAULT] | [ RESTRICT ] | [ CASCADE FORWARD ] | [ CASCADE BACKWARD ] | [ CASCADE ALL ] ]

Parameter Description

Table 1 REFRESH MATERIALIZED VIEW parameters

Parameter

Description

Value Range

materialized_view_name

Specifies the name of the materialized view to be refreshed.

-

DEFAULT

Specifies default refresh. It means that a single materialized view is not refreshed only until the base table has changed.

-

RESTRICT

Specifies forced refresh. It means that a materialized view is refreshed regardless of whether the base table has changed.

-

CASCADE FORWARD

Indicates a cascading refresh in the upward direction, starts by refreshing the current materialized view, and then refreshes the nested materialized views that depend it. The default refresh method is used.

-

CASCADE BACKWARD

Specifies cascading refresh in the downward direction. The system refreshes the lowest-level materialized view that the current materialized view relies on, then continues to refresh the nested materialized views that depend on the lowest-level one, and finally refreshes the current materialized view itself. The default refresh method is used.

-

CASCADE ALL

Specifies all cascading refreshes. First, cascading refreshes are performed downward, and then cascading refreshes are performed upward.

-

Examples

Refresh a materialized view.

1
REFRESH MATERIALIZED VIEW mv1;

Perform a cascading refresh in the downward direction for materialized views.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE t1(a int,b int);
CREATE MATERIALIZED VIEW mv1 AS SELECT * FROM t1 where a > 0;
CREATE MATERIALIZED VIEW mv2 AS SELECT * FROM mv1;
INSERT INTO t1 values(1,1),(2,2);
REFRESH MATERIALIZED VIEW mv2 cascade backward; -- After refreshing mv2, the data in mv1 was also updated.

SELECT * FROM mv1 ORDER BY 1,2;
 a | b
---+---
 1 | 1
 2 | 2
(2 rows)

SELECT * FROM mv2 ORDER BY 1,2;
 a | b
---+---
 1 | 1
 2 | 2
(2 rows)