Re: CREATE OR REPLACE MATERIALIZED VIEW

Hi,

On Fri, Aug 14, 2026 at 12:28 PM Paul A Jungwirth
<pj(at)illuminatedcomputing(dot)com> wrote:
> Sorry, I don't know what I was thinking here: of course you can put an
> index in a different tablespace than its table (or matview). Still, it
> does possibly break Tom's principle that CREATE OR REPLACE should give
> you the same result with or without a prior object. So if we are
> moving the matview's heap, maybe we should move its indexes too. Or
> maybe not: they have their own CREATE commands and are really separate
> objects. Whatever we do, it seems worth documenting.

I think CREATE OR REPLACE is the wrong syntax here. I think what
people would most often want to do is edit the query used to refresh
the query without disturbing the existing data, indexes, or other
ancillary properties of the view in any way, but just changing what
happens on next refresh. We have CREATE OR REPLACE FUNCTION because
replacing a function in its entirety is a sensible thing to do, but we
don't have CREATE OR REPLACE TABLE because tables contain data that
you're unlikely to want to throw out. Materialized views are more like
tables in this respect: they have data. Granted, it can be
regenerated, but that's probably expensive.

So I'd propose that this should be a form of ALTER MATERIALIZED VIEW,
and that it be defined as suggested above, just replacing the refresh
query.

I also agree with Tom's upthread comment that removing CREATE IF NOT
EXISTS is a nonstarter.

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