docs/v22.1/alter-schema.md at dff5c428246d4e18f2eacda09ab1589793b4b635 · MozzieWen/docs · GitHub

title	summary	toc	docs_area
ALTER SCHEMA	The ALTER SCHEMA statement modifies a user-defined schema in a database.	true	reference.sql

The ALTER SCHEMA statement modifies a user-defined schema. CockroachDB currently supports changing the name of the schema and the owner of the schema.

{% include {{ page.version.version }}/misc/schema-change-stmt-note.md %}

Syntax

{% remote_include https://raw.githubusercontent.com/cockroachdb/generated-diagrams/{{ page.release_info.crdb_branch_name }}/grammar_svg/alter_schema.html %}

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 "`.`".
`RENAME TO schema_name`	Rename the schema to `schema_name`. The new schema name must be unique within the database and follow these identifier rules.
`OWNER TO role_spec`	Change the owner of the schema to `role_spec`.

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.

Example

{% include {{page.version.version}}/sql/movr-statements.md %}

Rename a schema

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:

{% include_cached copy-clipboard.html %}

> CREATE USER max;

{% include_cached copy-clipboard.html %}

> CREATE SCHEMA org_one AUTHORIZATION max;

{% include_cached copy-clipboard.html %}

> 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:

{% include_cached copy-clipboard.html %}

> 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.

{% include_cached copy-clipboard.html %}

> DROP SCHEMA org_one;

{% include_cached copy-clipboard.html %}

> CREATE SCHEMA org_one;

To verify that the owner is now root, query the pg_catalog.pg_namespace and pg_catalog.pg_users tables:

{% include_cached copy-clipboard.html %}

> 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:

{% include_cached copy-clipboard.html %}

> ALTER SCHEMA org_one RENAME TO org_two;

{% include_cached copy-clipboard.html %}

> SHOW SCHEMAS;

     schema_name
----------------------
  crdb_internal
  information_schema
  org_two
  pg_catalog
  pg_extension
  public
(6 rows)

Change a schema's owner

Suppose that you access the SQL shell as user root, and create a new schema named org_one:

{% include_cached copy-clipboard.html %}

> CREATE SCHEMA org_one;

{% include_cached copy-clipboard.html %}

> 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.

{% include_cached copy-clipboard.html %}

> GRANT max TO root;

{% include_cached copy-clipboard.html %}

> GRANT CREATE ON DATABASE defaultdb TO max;

{% include_cached copy-clipboard.html %}

> 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:

{% include_cached copy-clipboard.html %}

> 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)

See also