Thread: CREATE OR REPLACE MATERIALIZED VIEW

Aidan Samuel <aidan.samuel@gmail.com> writes:
> How come table creation doesn't allow [ OR REPLACE ], and view creation
> doesn't allow [ IF NOT EXISTS ]? Is this just how the SQL spec defines
> things?

I think all that stuff is our own fault rather than something you can
find in the SQL spec.

For indivisible database objects such as functions, IMV the "or replace"
semantics (COR) is better than the "if not exists" semantics (CINE).
With COR, if the command succeeds then you know what properties the
object has: the ones you just gave.  With CINE, you don't really know
anything at all except that there's an object by that name.  So we've
generally preferred to implement COR if practical.

For tables, though, the tradeoffs seem pretty different.  In particular,
COR semantics would more or less imply throwing away the table contents,
and probably dropping existing foreign-key connections too, since none of
that could be inferred from the given command.  It's a little scary to
do that implicitly.  So we insist that if you really want to lose data,
you say DROP TABLE explicitly, and then you can create a fresh empty
table.

(Personally I think CINE is seldom anything but a foot-gun, and would
rather we didn't have it at all; but some folks insisted.)

> I also noticed that the syntax for creating VIEWS and creating MATERIALISED
> VIEWS is handled separately [1][2].

Again, those aren't really the same thing: a matview is a lot more like a
table than it is like a view, so we don't treat it the same.  I think
if you compare the text for the CREATE VIEW and CREATE MATVIEW man pages,
you'll agree that a merged version would be pretty confusing.  All the
options are different.

Admittedly, this is all judgment calls and experience, but that's
why it's like that.

            regards, tom lane