ALTER SCHEMA

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

The “ statement performs a schema change. For more information about how online schema changes work in CockroachDB, see .

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 .
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 . The new owner role must also have the CREATE on the database to which the schema belongs.

Examples

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 .

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

$ cockroach demo

Rename a schema

You cannot rename a schema if a in the schema is used by a or .

Suppose that you access the as user root, and max and 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.

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 as user root, and 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