CREATE_MATERIALIZED_VIEW(7pg18)

✨ CREATE MATERIALIZED VIEW(7) ✨

okay bestie, so this is the CREATE MATERIALIZED VIEW page from the PostgreSQL 18.6 docs, and it lives in section 7 of the manual 🎀 literally one of my fave pages because it's giving "save your query results, refresh them later, be iconic" energy. no cap.

---

🌸 NAME 🌸

CREATE_MATERIALIZED_VIEW — define a new materialized view 💖

---

🦋 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, and that's the whole vibe. the query gets executed and used to populate the view right at the moment you issue the command — unless you said WITH NO DATA, in which case it just... doesn't 🙈 — and you can refresh it later with REFRESH MATERIALIZED VIEW. periodt.

it's basically the cousin of CREATE TABLE AS, except it remembers the query it was initialized with, so it can be refreshed later whenever you feel like it. main character behavior honestly. a materialized view has a lot of the same properties as a regular table, but — and this is the important bit — there is no support for temporary materialized views. sorry bestie, that's just not a thing. 💀

also, CREATE MATERIALIZED VIEW requires CREATE privilege on the schema you're putting the materialized view in. no privilege, no view. that's the rules. 💅

---

💅 PARAMETERS 💅

• IF NOT EXISTS — don't throw an error if a materialized view with the same name already exists. instead a notice gets issued. but heads up babes: there is no guarantee that the materialized view that already exists is anything like the one you would've created. it could be a whole different situation. 😭
• table_name — the name of the materialized view you're creating, optionally schema-qualified. and it has to be distinct from the name of any other relation in the same schema — that's tables, sequences, indexes, views, materialized views, foreign tables, the whole friend group. no name collisions allowed, we don't do that here. ✨
• column_name — the name of a column in the new materialized view. if you don't provide column names, they just get taken from the output column names of the query. easy peasy. 🍓
• USING method — optional clause that says which table access method to use for storing the contents of the new materialized view. the method has to be an access method of type TABLE. see Chapter 62 for the deets. if you skip this option, the default table access method gets chosen for the new materialized view — check out default_table_access_method for more. 🌈
• WITH ( storage_parameter [= value] [, ... ] ) — this clause sets optional storage parameters for the new materialized view. check the Storage Parameters section in the CREATE TABLE (CREATE_TABLE(7)) docs for more info. literally all the parameters supported for CREATE TABLE are also supported here. see CREATE TABLE (CREATE_TABLE(7)) for more. 🎀
• TABLESPACE tablespace_name — tablespace_name is the name of the tablespace where the new materialized view gets created. if you don't specify one, default_tablespace gets consulted instead. 💜
• query — a SELECT, TABLE, or VALUES command. heads up though: this query runs inside a security-restricted operation. specifically, 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. iykyk. 😭
• WITH [ NO ] DATA — this clause decides whether the materialized view gets populated at creation time or not. if it doesn't, the materialized view gets flagged as unscannable and you can't query it until you run REFRESH MATERIALIZED VIEW. so like... it's just sitting there. unusable. waiting. 🙈

---

⭐ COMPATIBILITY ⭐

CREATE MATERIALIZED VIEW is a PostgreSQL extension. so like, don't go expecting it in other databases bestie, it's our thing. 💖

---

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

---

and that's the whole page!! this one's from PostgreSQL 18.6, dated 2026, and the page itself is CREATE MATERIALIZED VIEW(7) — go forth and materialize your views, bestie, you're gonna ate 💅✨🦋🌸