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 ] AS query
[ WITH [ NO ] DATA ]
create_matview
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
COLOCATED
Specify COLOCATED = 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