Re: CREATE OR REPLACE MATERIALIZED VIEW

On Fri, Aug 14, 2026 at 3:01 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.

I agree. And I think that's going to make people sad here...

> 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.

...precisely because of this. If the materialized view already exists,
it's pretty questionable whether you actually want to overwrite random
properties of it like the tablespace or storage options because you
did CREATE OR REPLACE, but it's *really* unlikely that you want to
discard the data. And yet, by the definition of CREATE OR REPLACE,
that is exactly what should happen.

> But I think Robert is correct that an ALTER command that does keep
> the old data is often going to be what's wanted.

It's also worth keeping in mind here that if we never add CREATE OR
REPLACE, people can still get that behavior with BEGIN; DROP IF
EXISTS; CREATE; COMMIT. The major disadvantage of that compared to
CREATE OR REPLACE is that CREATE OR REPLACE can handle the case where
there are dependencies on the replaced object, and DROP IF EXISTS will
have to either fail or drop the dependent objects in that case. But
replacing the object without disturbing dependencies can only be made
to work anyway if the column list hasn't changed in incompatible ways,
which is a real case, but not the only one. And if you do have that
use case, I think there's a really good chance you're going to be
happier with ALTER MATERIALIZED VIEW ... <change the query>. That way,
you have a choice of whether to refresh afterwards or not, and you
don't accidentally reset any ancillary properties.

--
Robert Haas
EDB: http://www.enterprisedb.com