On Fri, Aug 14, 2026 at 3:01 PM Tom Lane <[email protected]> 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
