The ALTER SCHEMA
statement modifies a user-defined schema. CockroachDB currently supports changing the name of the schema and the owner of the schema.
The ALTER SCHEMA
statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.
Syntax
Parameters
Parameter | Description |
---|---|
name name.name |
The name of the schema to alter, or the name of the database containing the schema and the schema name, separated by a ". ". |
schema_new_name |
The name of the new schema. The new schema name must be unique within the database and follow these identifier rules. |
role_spec |
The role to set as the owner of the schema. |
Required privileges
- To rename a schema, the user must be the owner of the schema.
- To change the owner of a schema, the user must be the current owner of the schema and a member of the new owner role. The new owner role must also have the
CREATE
privilege on the database to which the schema belongs.
Examples
Setup
To follow along, run cockroach demo
to start a temporary, in-memory cluster with the movr
sample dataset preloaded:
$ cockroach demo
Rename a schema
You cannot rename a schema if a table in the schema is used by a view or user-defined function.
Suppose that you access the SQL shell as user root
, and create a new user max
and a schema org_one
with max
as the owner:
CREATE USER max;
CREATE SCHEMA org_one AUTHORIZATION max;
SHOW SCHEMAS;
schema_name
----------------------
crdb_internal
information_schema
org_one
pg_catalog
pg_extension
public
(6 rows)
Now, suppose you want to rename the schema:
ALTER SCHEMA org_one RENAME TO org_two;
ERROR: must be owner of schema "org_one"
SQLSTATE: 42501
Because you are executing the ALTER SCHEMA
command as a non-owner of the schema (i.e., root
), CockroachDB returns an error.
Drop the schema and create it again, this time with root
as the owner.
DROP SCHEMA org_one;
CREATE SCHEMA org_one;
To verify that the owner is now root
, query the pg_catalog.pg_namespace
and pg_catalog.pg_users
tables:
SELECT
nspname, usename
FROM
pg_catalog.pg_namespace
LEFT JOIN pg_catalog.pg_user ON pg_namespace.nspowner = pg_user.usesysid
WHERE
nspname LIKE 'org_one';
nspname | usename
----------+----------
org_one | root
(1 row)
As its owner, you can rename the schema:
ALTER SCHEMA org_one RENAME TO org_two;
SHOW SCHEMAS;
schema_name
----------------------
crdb_internal
information_schema
org_two
pg_catalog
pg_extension
public
(6 rows)
Change the owner of a schema
Suppose that you access the SQL shell as user root
, and create a new schema named org_one
:
CREATE SCHEMA org_one;
SHOW SCHEMAS;
schema_name
----------------------
crdb_internal
information_schema
org_one
pg_catalog
pg_extension
public
(6 rows)
Now, suppose that you want to change the owner of the schema org_one
to an existing user named max
. To change the owner of a schema, the current owner must belong to the role of the new owner (in this case, max
), and the new owner must have CREATE
privileges on the database.
GRANT max TO root;
GRANT CREATE ON DATABASE defaultdb TO max;
ALTER SCHEMA org_one OWNER TO max;
To verify that the owner is now max
, query the pg_catalog.pg_namespace
and pg_catalog.pg_users
tables:
SELECT
nspname, usename
FROM
pg_catalog.pg_namespace
LEFT JOIN pg_catalog.pg_user ON pg_namespace.nspowner = pg_user.usesysid
WHERE
nspname LIKE 'org_one';
nspname | usename
----------+----------
org_one | max
(1 row)