info > CREATE_MATERIALIZED_VIEW

man perldoc info pydoc3 ri search

📖 NAME

CREATE_MATERIALIZED_VIEW — define a new materialized view

🚀 Quick Reference

Use Case	Command	Description
Create a materialized view	CREATE MATERIALIZED VIEW view_name AS select_query;	Defines and populates a new materialized view.
Create an empty materialized view	CREATE MATERIALIZED VIEW view_name AS select_query WITH NO DATA;	Defines the view but marks it as unscannable until refreshed.
Create with specific columns	CREATE MATERIALIZED VIEW view_name (col1, col2) AS select_query;	Creates a materialized view with specific column names.
Create with storage parameters	CREATE MATERIALIZED VIEW view_name WITH (fillfactor = 90) AS select_query;	Creates a materialized view with custom storage settings.
Create in a specific tablespace	CREATE MATERIALIZED VIEW view_name TABLESPACE fast_space AS select_query;	Stores the materialized view data in a specific tablespace.
Create if not exists	CREATE MATERIALIZED VIEW IF NOT EXISTS view_name AS select_query;	Creates a materialized view only if it doesn't already exist.

📝 SYNOPSIS

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name
    [ (column_name [, ...] ) ]
    [ USING method ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ]

📖 DESCRIPTION

CREATE MATERIALIZED VIEW defines a materialized view of a query. The query is executed and used to populate the view at the time the command is issued (unless WITH NO DATA is used) and may be refreshed later using REFRESH MATERIALIZED VIEW.

CREATE MATERIALIZED VIEW is similar to CREATE TABLE AS, except that it also remembers the query used to initialize the view, so that it can be refreshed later upon demand. A materialized view has many of the same properties as a table, but there is no support for temporary materialized views.

CREATE MATERIALIZED VIEW requires CREATE privilege on the schema used for the materialized view.

⚙️ PARAMETERS

• IF NOT EXISTS
  🛡️ Do not throw an error if a materialized view with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing materialized view is anything like the one that would have been created.
• table_name
  🏷️ The name (optionally schema-qualified) of the materialized view to be created.
• column_name
  📛 The name of a column in the new materialized view. If column names are not provided, they are taken from the output column names of the query.
• USING method
  💾 This optional clause specifies the table access method to use to store the contents for the new materialized view; the method needs be an access method of type TABLE. See Chapter 61 for more information. If this option is not specified, the default table access method is chosen for the new materialized view. See default_table_access_method for more information.
• WITH ( storage_parameter [= value] [, ... ] )
  ⚙️ This clause specifies optional storage parameters for the new materialized view; see Storage Parameters in the CREATE TABLE (CREATE_TABLE(7)) documentation for more information. All parameters supported for CREATE TABLE are also supported for CREATE MATERIALIZED VIEW. See CREATE TABLE (CREATE_TABLE(7)) for more information.
• TABLESPACE tablespace_name
  💽 The tablespace_name is the name of the tablespace in which the new materialized view is to be created. If not specified, default_tablespace is consulted.
• query
  🔍 A SELECT, TABLE, or VALUES command. This query will run within a security-restricted operation; in particular, calls to functions that themselves create temporary tables will fail.
• WITH [ NO ] DATA
  📊 This clause specifies whether or not the materialized view should be populated at creation time. If not, the materialized view will be flagged as unscannable and cannot be queried until REFRESH MATERIALIZED VIEW is used.

🔗 COMPATIBILITY

CREATE MATERIALIZED VIEW is a PostgreSQL extension.

📚 SEE ALSO

• ALTER MATERIALIZED VIEW (ALTER_MATERIALIZED_VIEW(7))
• CREATE TABLE AS (CREATE_TABLE_AS(7))
• CREATE VIEW (CREATE_VIEW(7))
• DROP MATERIALIZED VIEW (DROP_MATERIALIZED_VIEW(7))
• REFRESH MATERIALIZED VIEW (REFRESH_MATERIALIZED_VIEW(7))

---

PostgreSQL 14.23 2026 CREATE MATERIALIZED VIEW(7)

📖 NAME 🚀 Quick Reference 📝 SYNOPSIS 📖 DESCRIPTION ⚙️ PARAMETERS 🔗 COMPATIBILITY 📚 SEE ALSO