v2.23 Preview Preview release - not for production v2024.1 STS Stable release with standard-term support v2.20 LTS Stable release with long-term support v2.18 STS Stable release with standard-term support v2.14 LTS Stable release with long-term support Unsupported versions

REFRESH MATERIALIZED VIEW

Synopsis

Use the REFRESH MATERIALIZED VIEW statement to replace the contents of a materialized view.

Syntax

refresh_matview ::= REFRESH MATERIALIZED VIEW [ CONCURRENTLY ]  
                    matview_name [ WITH [ NO ] DATA ]

refresh_matview

REFRESHMATERIALIZEDVIEWCONCURRENTLYmatview_nameWITHNODATA

Semantics

Replace the contents of a materialized view named matview_name.

WITH DATA

If WITH DATA (default) is specified, the view's query is executed to obtain the new data and the materialized view's contents are updated.

WITH NO DATA

If WITH NO DATA is specified, the old contents of the materialized view are discarded and the materialized view is left in an unscannable state.

CONCURRENTLY

This option may be faster in the cases where a small number of rows require updates. Without this option, a refresh that updates a large number of rows will tend to use fewer resources and complete quicker. This option is only permitted when there is at least one UNIQUE index on the materialized view, and when the materialized view is populated. CONCURRENTLY AND WITH NO DATA cannot be used together. Moreover, the CONCURRENTLY option can also not be used when there are rows where all the columns are null. In both scenarios, refreshing the materialized view will raise an error.

Examples

Basic example.

yugabyte=# CREATE TABLE t1(a int4);
yugabyte=# CREATE MATERIALIZED VIEW m1 AS SELECT * FROM t1;
yugabyte=# INSERT INTO t1 VALUES (1);
yugabyte=# SELECT * FROM m1;

 a
---
(0 rows)

yugabyte=# REFRESH MATERIALIZED VIEW m1;
yugabyte=# SELECT * FROM m1;

 a
---
 1
(1 row)

Limitations

  • Materialized views must be refreshed manually using the REFRESH command. Automatic refreshes are not supported.
  • It is currently not possible to refresh materialized views inside an explicit transaction. (See https://github.com/yugabyte/yugabyte-db/issues/1404)

See also