ALTER ... RENAME
ALTER ... RENAME renames an item, albeit with some limitations.
Note that renaming schemas and databases are in development (#3680).
|name||The identifier of the item you want to rename.|
|new_name||The new identifier you want the item to use.|
Because of Materialize’s SQL engine, we currently only support renaming items in limited contexts.
You cannot rename items if:
They are not uniquely qualified among all references to their identifier in all dependent views.
For example, if you have:
- Two views named
v1in two different databases (
d2), but they both use the same schema name (
- Both views named
v1are used in another view’s query
You can only rename either view named
v1if every dependent view’s query that contains references to both views fully qualifies all references, e.g.
CREATE VIEW v2 AS SELECT * FROM db1.s1.v1 JOIN db2.s1.v1 ON db1.s1.v1.a = db2.s1.v1
If these two views were placed in schemas using distinct identifiers, you would only need to qualify their references with schemas instead of databases and schemas.
- Two views named
Any dependent query references a database, schema, or column that uses the same identifier.
In the following examples,
v1could not be renamed:
CREATE VIEW v3 AS SELECT * FROM v1 JOIN v2 ON v1.a = v2.v1
CREATE VIEW v4 AS SELECT * FROM v1 JOIN v1.v2 ON v1.a = v2.a
They are system clusters, such as
mz_introspection, or replicas in system clusters.
New name limitations
You cannot rename an item to any identifier used in a dependent query, whether that identifier is used implicitly or explicitly.
Consider this example:
CREATE VIEW v5 AS SELECT * FROM d1.s1.v2 JOIN v1 ON v1.a = v2.b
You could not rename
public(implicitly referenced by
materialize.public.v1using the default database and schema)
However, you could rename
v1 to any other legal identifier.
VIEWS ------- v1
ALTER VIEW v1 RENAME TO v2; SHOW VIEWS;
VIEWS ------- v2
The privileges required to execute this statement are:
- Ownership of the object being renamed.