REFRESH_MATERIALIZED_VIEW(7pg13)

💅 REFRESH_MATERIALIZED_VIEW 💅

okay so bestie, welcome to the REFRESH_MATERIALIZED_VIEW page from the PostgreSQL 13.23 Documentation ✨ this is the one that's gonna teach you how to swap out everything inside a materialized view, no cap 🫶

---

✨ NAME ✨

REFRESH_MATERIALIZED_VIEW — replace the contents of a materialized view 🔥

---

🎀 SYNOPSIS 🎀

REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
    [ WITH [ NO ] DATA ]

---

🦋 DESCRIPTION 🦋

okay so REFRESH MATERIALIZED VIEW completely replaces the contents of a materialized view. Like, out with the old, in with the new, periodt 💅

to run this command you must be the owner of the materialized view — no owner, no refresh, that's the vibe check 🌸

the old contents are discarded. gone. bye 👋

if WITH DATA is specified (or just defaults, because it's the default), the backing query gets executed to provide the new data, and the materialized view is left in a scannable state — meaning you can actually read from it ✨

if WITH NO DATA is specified, no new data is generated and the materialized view is left in an unscannable state 🫣

⚠️ important: CONCURRENTLY and WITH NO DATA may not be specified together. like, at all. they do not mix, bestie 😭

---

💖 PARAMETERS 💖

• CONCURRENTLY — refresh the materialized view without locking out concurrent selects on that view. when you skip this option, a refresh that affects a lot of rows will tend to use fewer resources and complete more quickly — but it could block other connections that are trying to read from the materialized view 😬 this option may be faster in cases where a small number of rows are affected.

so here's the catch, and it's a big one 🚨 this option is only allowed if there's at least one UNIQUE index on the materialized view which uses only column names and includes all rows — meaning it must not be an expression index or include a WHERE clause. iykyk.

also, this option may not be used when the materialized view is not already populated 🙈

and even with this option, only one REFRESH at a time may run against any one materialized view. one. single. one. 👑

• name — the name of the materialized view to refresh, optionally schema-qualified 🏷️

---

🌈 NOTES 🌈

if there's an ORDER BY clause in the materialized view's defining query, the original contents of the materialized view will be ordered that way — cute! 🌸 but REFRESH MATERIALIZED VIEW does not guarantee to preserve that ordering. so like... don't count on it, fr fr 💀

---

🍓 EXAMPLES 🍓

this command will replace the contents of the materialized view called order_summary using the query from the materialized view's definition, and leave it in a scannable state ✨:

REFRESH MATERIALIZED VIEW order_summary;

this command will free storage associated with the materialized view annual_statistics_basis and leave it in an unscannable state 🫣:

REFRESH MATERIALIZED VIEW annual_statistics_basis WITH NO DATA;

---

⭐ COMPATIBILITY ⭐

REFRESH MATERIALIZED VIEW is a PostgreSQL extension 💜 (so don't go looking for it in other databases, bestie)

---

💜 SEE ALSO 💜

• CREATE MATERIALIZED VIEW (CREATE_MATERIALIZED_VIEW(7)) 🎀
• ALTER MATERIALIZED VIEW (ALTER_MATERIALIZED_VIEW(7)) 🦋
• DROP MATERIALIZED VIEW (DROP_MATERIALIZED_VIEW(7)) 🌸

---

and that's the whole page, icon 👑 this absolute classic is from PostgreSQL 13.23, dated 2025, officially filed under REFRESH ...IZED VIEW(7) — go refresh something and slay 💅✨🫶