CREATE_MATERIALIZED_VIEW(7pg16)

💅 CREATE MATERIALIZED VIEW 💅

okay so this is the CREATE MATERIALIZED VIEW(7) page from the PostgreSQL 16 docs, and bestie, it's lowkey one of the most useful things in here. buckle up 🎀

---

✨ NAME ✨

CREATE_MATERIALIZED_VIEW — define a new materialized view. that's it, that's the whole vibe ✨

---

🦋 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 clauses. every single one of them is optional except table_name, AS query and the whole CREATE MATERIALIZED VIEW part. no cap 💅

---

💖 DESCRIPTION 💖

CREATE MATERIALIZED VIEW defines a materialized view of a query. it's literally a snapshot that remembers the query, and it ate fr fr.

so here's the deal: the query gets executed and used to populate the view at the time the command is issued (unless you use WITH NO DATA), and you can refresh it later with REFRESH MATERIALIZED VIEW. iconic behaviour.

CREATE MATERIALIZED VIEW is similar to CREATE TABLE AS, except — and this is the main character detail 🫶 — it also remembers the query used to initialize the view, so it can be refreshed later upon demand. a materialized view has many of the same properties as a table, but ngl there is no support for temporary materialized views. sad but true 😭

heads up: CREATE MATERIALIZED VIEW requires CREATE privilege on the schema used for the materialized view. don't forget it or you'll be staring at an error like 👁️👄👁️

---

🌸 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 instead. and bestie, listen: 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 💀
• 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 name-stealing 🙈
• column_name — the name of a column in the new materialized view. if you don't provide column names, they're just taken from the output column names of the query. she inherits it, periodt 💅
• 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 63 for more information. if this option isn't specified, the default table access method is chosen for the new materialized view — see default_table_access_method for more info 🔥
• 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. yes, she's flexible 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 needs a home 🏠
• query — a SELECT, TABLE, or VALUES command. important: this query will run within a security-restricted operation; in particular, calls to functions that themselves create temporary tables will fail. so like, don't try that 🫶
• 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 really there yet, yk? 😭

---

🌈 COMPATIBILITY 🌈

CREATE MATERIALIZED VIEW is a PostgreSQL extension. yeah, she's homegrown 💜

---

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

go read them, they're all slay in their own way 📚✨

---

and that's the whole page bestie!! 🎀 this one came straight out of the PostgreSQL 16.15 documentation, dated 2026 — so if you see CREATE MA...IZED VIEW(7) stamped at the bottom of a terminal somewhere, that's just her little signature 👑💖 go forth and materialize 💅