---
title: "CREATE MATERIALIZED VIEW"
url: "https://docs.yugabyte.com/stable/api/ysql/the-sql-language/statements/ddl_create_matview/"
---

# CREATE MATERIALIZED VIEW

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

## Synopsis

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

## Syntax

- [![Grammar Icon](/icons/file-lines.svg)Grammar](#grammar-1)
- [![Diagram Icon](/icons/diagram.svg)Diagram](#diagram-1)

```output.ebnf
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

CREATEMATERIALIZEDVIEWIFNOTEXISTS[matview\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#matview-name)(,[column\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#column-name))WITH([storage\_parameters](/stable/api/ysql/syntax_resources/grammar_diagrams#storage-parameters))TABLESPACE[tablespace\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#tablespace-name)AS[subquery](/stable/api/ysql/syntax_resources/grammar_diagrams#subquery)WITHNODATA

## 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.

```plpgsql
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;
```

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

```plpgsql
yugabyte=# SELECT * FROM m1;
```

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

## Limitations

- Materialized views are not supported in YCQL

## See also

- [REFRESH MATERIALIZED VIEW](/stable/api/ysql/the-sql-language/statements/ddl_refresh_matview "REFRESH MATERIALIZED VIEW")
- [DROP MATERIALIZED VIEW](/stable/api/ysql/the-sql-language/statements/ddl_drop_matview "DROP MATERIALIZED VIEW")