alter-schema.md

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

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

Syntax

ALTER SCHEMA ... RENAME TO <newschemaname>
ALTER SCHEMA ... OWNER TO {<newowner> | CURRENT_USER | SESSION_USER }

Parameters

Parameter	Description
RENAME TO ...	Rename the schema.
OWNER TO ...	Change the owner of the schema. You can specify the new owner with a string literal or the CURRENT_USER or SESSION_USER keywords.

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

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 copy-clipboard.html %}

> CREATE USER max;

{% include copy-clipboard.html %}

> CREATE SCHEMA org_one AUTHORIZATION max;

{% include copy-clipboard.html %}

> SHOW SCHEMAS;

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

{% include 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)

Now, suppose you want to rename the schema:

{% include 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 with root as the owner.

{% include copy-clipboard.html %}

> DROP SCHEMA org_one;

{% include copy-clipboard.html %}

> CREATE SCHEMA org_one;

{% include 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)

Rename the schema as its owner:

{% include copy-clipboard.html %}

> ALTER SCHEMA org_one RENAME TO org_two;

{% include 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 copy-clipboard.html %}

> CREATE SCHEMA org_one;

{% include 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)

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 copy-clipboard.html %}

> GRANT max TO root;

{% include copy-clipboard.html %}

> GRANT CREATE ON DATABASE defaultdb TO max;

{% include copy-clipboard.html %}

> ALTER SCHEMA org_one OWNER TO max;

{% include 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

• CREATE SCHEMA
• SHOW SCHEMAS
• DROP SCHEMA