Re: CREATE OR REPLACE MATERIALIZED VIEW

On Fri, Aug 21, 2026 at 9:13 AM Isaac Morland <isaac(dot)morland(at)gmail(dot)com> wrote:
> Yes, I can see the logic of this.
>
> On the other hand, what do we mean by "properties of the object"? If we mean "what pg_dump would dump", I don't believe the data stored in a materialized view affect what gets dumped.
>
> Perhaps there could be an option — "WITH EXISTING DATA". I'm actually a bit skeptical of how often this would be wanted. If I’ve updated the query that defines the contents of the view, why would I still want the old data that were computed used the old query? I can think of lots of scenarios, actually, but conceptually it doesn't feel like the safe default as after updating the query the naive expectation is that the contents have been updated.

The data doesn't get dumped because we can't restore it, but I think
it's still a property of the object. We all seem to agree that
idempotency is a goal here, and whether a materialized view has data
and what data it has is certainly a detectable property of the object.

>> In particular, ISTM that if we invent CREATE OR REPLACE MATERIALIZED
>> VIEW, then the view content should be either computed afresh or left
>> empty (depending on WITH NO DATA); it should never leave stale data.
>> But I think Robert is correct that an ALTER command that does keep
>> the old data is often going to be what's wanted.
>
> In any case, I'm not too hung up on what the syntax looks like or the exact behaviour. But I do think if we're going to do something like this that there should be a command which will create the view if it doesn't exist, and update the query if it does. Otherwise it doesn't help at all with writing idempotent schema definitions, which is my main reason for using CREATE OR REPLACE.

One option could be CREATE MATERIALIZED VIEW IF NOT EXISTS with the
correct column types, and then ALTER MATERIALIZED VIEW to set the
query to whatever you want. On first execution, the first creates the
view; on every execution, the latter updates the view to the current
query. What's unclear to me is what refresh behavior you're hoping
for. If you want to refresh always after an update, or never, that's
pretty straightforward. If you want to refresh only when the query is
actually different, this approach doesn't lend itself to that.

I feel like what someone wants here might depend on how they're
imagining the query getting updated. If I were writing a DDL update
script, I think I'd be looking for the script to complete quickly, and
any refreshes to be deferred until later. Maybe I'd have the refresh
in crontab, and I'd try to arrange the update and the application
changes so that the upgrade was smooth, e.g. the user-facing
application can tolerate either the pre-refresh or post-refresh
contents of the materialized view. If you are trying to implement a
hard cutover, you would want the refresh to be immediate, but I'm
still not sure I'd want the refresh to happen in the middle of my DDL
update script. I think I might want to put enough smarts into the
script -- e.g. a schema version stored somewhere in the database --
that I could defer the refresh until after the rest of the DDL changes
had been completed. Otherwise, you might spend a lot of time cranking
through materialized view updates and then have some later step fail,
which would be sad. But if this is just a very small-scale application
and refreshing the materialized view only takes a few seconds, you
might prefer doing it on the fly. But in that case maybe it also
wouldn't be that bad if you had unnecessary refreshes sometimes?

It's a little hard to think through all the possible scenarios here,
and it's hard to cater to everything that someone might want for their
particular use case. I think it's fine if we end up with a few
different DDL commands here to cater to different scenarios; not
everybody's scenario is the same, and changes can be worth making even
if not everybody will use or like the resulting features. But the
project bears the maintenance costs of whatever we add, and anything
we remove, so we also can't go too crazy with it. A command that's
beyond perfect for one particular use case but hard for everybody else
to understand isn't really a win overall.

--
Robert Haas
Databricks