CREATE OR REPLACE MATERIALIZED VIEW
I've been looking over the syntax for creating various database objects:
tables, views, functions, etc., and I wondered why there seem to be some
discrepancies.
For example, table creation syntax features [ IF NOT EXISTS ] but doesn't
feature [ OR REPLACE ], function creation syntax is the inverse and has [
OR REPLACE ], but no [ IF NOT EXISTS ].
I also noticed that the syntax for creating VIEWS and creating MATERIALISED
VIEWS is handled separately [1]https://www.postgresql.org/docs/current/sql-createview.html[2]https://www.postgresql.org/docs/current/sql-creatematerializedview.html.
I was expecting to see one page of documentation with the syntax listed as
something like: CREATE [ OR REPLACE ] [ MATERIALIZED ] VIEW [ IF NOT EXISTS
] view_name.
Is this because a materialized view is actually a table and thus inherits
the table creation syntax instead of the view creation syntax?
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?
Thanks,
Aidan.
[1]: https://www.postgresql.org/docs/current/sql-createview.html
[2]: https://www.postgresql.org/docs/current/sql-creatematerializedview.html
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
On Thu, 10 Jan 2019 at 12:13, Tom Lane <tgl@sss.pgh.pa.us> wrote:
For indivisible database objects such as functions, IMV the "or replace"
semantics (COR) is better than the "if not exists" semantics (CINE)...For tables, though, the tradeoffs seem pretty different...
Thanks Tom, your explanation makes a lot of sense.