CREATE_MATERIALIZED_VIEW(7pg17)

💅 CREATE_MATERIALIZED_VIEW — define a new materialized view

okay so bestie, this is a PostgreSQL 17.11 Documentation page and it's literally about making a materialized view. picture a view but it actually remembers stuff. main character energy fr fr ✨

---

✨ 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 🎀

So CREATE MATERIALIZED VIEW defines a materialized view of a query. That's it, that's the whole vibe. The query gets executed and used to populate the view right when you issue the command (unless you did WITH NO DATA, sneaky) — and you can refresh it later with REFRESH MATERIALIZED VIEW. obsessed.

It's lowkey similar to CREATE TABLE AS, except — and this is the iconic part — it also remembers the query used to initialize the view, so it can be refreshed later whenever you want. ✨ A materialized view has many of the same properties as a table, but just so we're clear: there is no support for temporary materialized views. yeah. none. don't ask.

Also heads up, you need CREATE privilege on the schema used for the materialized view. no privilege, no view. periodt.

---

🦋 PARAMETERS 🦋

• IF NOT EXISTS — Do not throw an error if a materialized view with the same name already exists. Instead a notice is issued. ⚠️ but like, note that there is no guarantee that the existing materialized view is anything like the one that would have been created. like, at all. it could be a completely different creature.
• table_name — The name (optionally schema-qualified) of the materialized view to be created. The name must be distinct from the name of any other relation (table, sequence, index, view, materialized view, or foreign table) in the same schema. no duplicates in this house 🙅
• column_name — The name of a column in the new materialized view. If you don't provide column names, they're taken from the output column names of the query. borrowed, but make it fashion 💅
• 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. it'll go find a home for you 🏠
• 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. Also, while the query is running, the search_path is temporarily changed to pg_catalog, pg_temp. 😭 yes really, it's for safety.
• 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. so like... it exists but it's empty and unusable. the audacity honestly 💀

---

🌈 COMPATIBILITY 🌈

CREATE MATERIALIZED VIEW is a PostgreSQL extension. yeah we made this one ourselves, we're built different 👑

---

🌸 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))

---

okay that's the whole page, you ate that 🫶 this was PostgreSQL 17.11, dated 2026, and honestly? iconic. now go populate something. 🧚✨