Re: CREATE OR REPLACE MATERIALIZED VIEW

On Fri, 14 Aug 2026 at 15:01, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Isaac Morland <isaac(dot)morland(at)gmail(dot)com> writes:
> > This sounds like CREATE OR REPLACE to me, even if there is something to
> > explain in the documentation about what happens to the existing data
> > (assuming it is currently populated). I don't think of CREATE OR REPLACE
> as
> > completely re-creating an object anyhow. Certainly it can't arbitrarily
> > replace a function (can't change return type) nor a view (can't remove or
> > change type of a column). It's OK if not all possible changes are
> supported
> > by the "OR REPLACE" part of the command.
>
> To my mind, the formal requirement for CREATE OR REPLACE should be
> "if the command succeeds, the resulting object properties are
> identical to what they'd be if we were creating it fresh" -- basically
> a form of idempotency. It's okay to fail when there are reasons why
> we can't or shouldn't make that so.
>

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.

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.