REFRESH_MATERIALIZED_VIEW(7pg14)

📦 version 14.24, in opensuse-leap 16.0

💅 REFRESH MATERIALIZED VIEW — the glow-up command

okay bestie, so this is the REFRESH_MATERIALIZED_VIEW(7) page from the PostgreSQL 14.24 documentation, and it's literally about giving your materialized view a whole new look without losing the plot 🎀

✨ NAME ✨

REFRESH_MATERIALIZED_VIEW — replace the contents of a materialized view. that's it, that's the whole vibe. out with the old, in with the new 🦋

🫶 SYNOPSIS 🫶

REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
    [ WITH [ NO ] DATA ]
not me putting the exact syntax in a code block so you can't mess it up 💀 copy it character for character, bestie. slay responsibly.

🌸 DESCRIPTION 🌸

REFRESH MATERIALIZED VIEW completely replaces the contents of a materialized view. like a full closet clean-out, no survivors, the old contents are discarded.

to even run this command you gotta be the owner of the materialized view — no sneaking into someone else's wardrobe, periodt 👑

so here's the tea on the data part:

  • if WITH DATA is specified (or it just defaults, because of course it does), the backing query gets executed to provide the new data, and the materialized view is left in a scannable state ✨
  • if WITH NO DATA is specified, no new data is generated and the materialized view is left in an unscannable state 🙈
🚨 important vibe check: CONCURRENTLY and WITH NO DATA may not be specified together. they simply cannot coexist, it's giving incompatible star signs.

💖 PARAMETERS 💖

  • CONCURRENTLY — refresh the materialized view without locking out concurrent selects on it. iconic behavior, honestly. without 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 🔥

this option is only allowed if there is at least one UNIQUE index on the materialized view which uses only column names and includes all rows — that is, it must not be an expression index, and it must not include a WHERE clause. no shortcuts, no loopholes, the index has to be legit 💅

this option may not be used when the materialized view is not already populated. not me showing up uninvited to a party that hasn't started yet.

even with this option, only one REFRESH at a time may run against any one materialized view. one at a time, bestie, we're not doing chaos here 🧚

  • name — the name (optionally schema-qualified!) of the materialized view you wanna refresh. yes, you can be specific about where it lives, and honestly, respect.

⭐ NOTES ⭐

if there is an ORDER BY clause in the materialized view's defining query, the original contents of the materialized view will be ordered that way — but REFRESH MATERIALIZED VIEW does not guarantee to preserve that ordering. so like… it might look cute and sorted, but do not come for it later if it doesn't. that's not a promise, that's a vibe 🦋

🌈 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;

see how the second one is basically a storage-clearing era? iconic. she's empty, she's free, she's unscannable 💀✨

🎀 COMPATIBILITY 🎀

REFRESH MATERIALIZED VIEW is a PostgreSQL extension. not a standard SQL thing, it's our thing, iykyk 👑

💜 SEE ALSO 💜

and that's the whole page, bestie 🫶 this was PostgreSQL 14.24, dated 2026. go forth and refresh your views, literally and emotionally 💅🌸

📦 where this page comes from