ALTER SCHEMA

On this page

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

Syntax

Parameters

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

Setup

The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App.

To follow along, run cockroach demo to start a temporary, in-memory cluster with the movr dataset preloaded:

$ cockroach demo

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:

> 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 a schema's owner

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)

See also

• CREATE SCHEMA
• SHOW SCHEMAS
• DROP SCHEMA