Hi, On Fri, Aug 14, 2026 at 12:28 PM Paul A Jungwirth <[email protected]> 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
