CREATE_MATERIALIZED_VIEW(7pg13)

💅 CREATE MATERIALIZED VIEW (7pg13) — the PostgreSQL 13.23 glow-up guide

okay bestie, so we're talking CREATE MATERIALIZED VIEW from the PostgreSQL 13.23 Documentation and honestly? she's iconic. let's get into it 🎀

---

✨ NAME ✨

CREATE_MATERIALIZED_VIEW — define a new materialized view 💖 (yes, like a view but she remembers things, we love a girl with memory)

---

🦋 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 ]

look at all those optional brackets — she's flexible, she's giving main character energy, she lets you skip the extras if you're not feeling them 💅

---

🌸 DESCRIPTION 🌸

so CREATE MATERIALIZED VIEW defines a materialized view of a query. fr fr. and here's the tea: the query actually gets executed and used to populate the view at the exact moment you issue the command (unless you hit it with WITH NO DATA, more on that later). then you can refresh her later any time using REFRESH MATERIALIZED VIEW. she's lowkey self-care for your query results ✨

CREATE MATERIALIZED VIEW is super similar to CREATE TABLE AS — like, sisters — EXCEPT that she also remembers the query you used to initialize her, so she can be refreshed later on demand. that's the whole vibe. iykyk.

a materialized view has many of the same properties as a table, but heads up: there is no support for temporary materialized views. not a thing. don't try it. 🙈

---

💜 PARAMETERS 💜

okay this is the part where we go through every single one, no skipping, because every detail matters bestie 🫶

• IF NOT EXISTS — do not throw an error if a materialized view with the same name already exists. a notice gets issued in this case instead. but real talk: note that there is no guarantee that the existing materialized view is anything like the one that would have been created. she might be a completely different girl. 😭 proceed with caution.
• table_name — the name (optionally schema-qualified) of the materialized view to be created. that's your girl's name 💖
• 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. so she just inherits them, cute 🎀
• 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 60 for more info. if you don't specify this option, the default table access method is chosen for the new materialized view. see default_table_access_method for more info. slay, defaults 💅
• 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 info. literally all parameters supported for CREATE TABLE are also supported for CREATE MATERIALIZED VIEW. see CREATE TABLE (CREATE_TABLE(7)) for more information. she's generous like that ✨
• 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. she'll find a home either way 🏠
• query — a SELECT(7), TABLE, or VALUES(7) command. this query will run within a security-restricted operation; in particular, calls to functions that themselves create temporary tables will fail. ⚠️ so like, keep that in mind — no sneaky temp tables in there.
• 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 she exists but she's not ready yet — a vibe check she'll fail until you refresh her. 😭🔥

---

🌈 COMPATIBILITY 🌈

CREATE MATERIALIZED VIEW is a PostgreSQL extension. that's it, that's the whole section. homegrown, proprietary to the fam, periodt. 👑

---

⭐ SEE ALSO ⭐

go read these next, bestie, they're all connected 🧚:

• 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, no cap 💖 this was PostgreSQL 13.23, dated 2025 — go forth and materialize, you absolute icon 🫶✨🎀