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

CREATE MATERIALIZED VIEW

Synopsis

Use the CREATE MATERIALIZED VIEW statement to create a materialized view.

Syntax

create_matview ::= CREATE MATERIALIZED VIEW [ IF NOT EXISTS ]  
                   matview_name [ ( column_name [ , ... ] ) ]  
                   [ WITH ( storage_parameters ) ]  
                   [ TABLESPACE tablespace_name ]  AS subquery 
                   [ WITH [ NO ] DATA ]

create_matview

CREATEMATERIALIZEDVIEWIFNOTEXISTSmatview_name(,column_name)WITH(storage_parameters)TABLESPACEtablespace_nameASsubqueryWITHNODATA

Semantics

Create a materialized view named matview_name. If matview_name already exists in the specified database, an error will be raised unless the IF NOT EXISTS clause is used.

Tablespace

Used to specify the tablespace for the materialized view.

Storage parameters

COLOCATION

Specify COLOCATION = true for the materialized view to be colocated. The default value of this option is false.

Examples

Basic example.

yugabyte=# CREATE TABLE t1(a int4, b int4);
yugabyte=# INSERT INTO t1 VALUES (2, 4), (3, 4);
yugabyte=# CREATE MATERIALIZED VIEW m1 AS SELECT * FROM t1 WHERE a = 3;
yugabyte=# SELECT * FROM t1;

 a | b
---+---
 3 | 4
 2 | 4
(2 rows)

yugabyte=# SELECT * FROM m1;

 a | b
---+---
 3 | 4
(1 row)

Limitations

  • Materialized views are not supported in YCQL

See also