ALTER SCHEMA

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)

Statements

Syntax

Data Types

Spatial Data

Constraints

Syntax

Parameters

Required privileges

Examples

Setup

Rename a schema

Change the owner of a schema

See also

​Syntax

​Parameters

​Required privileges

​Examples

​Setup

​Rename a schema

​Change the owner of a schema

​See also

Syntax

Parameters

Required privileges

Examples

Setup

Rename a schema

Change the owner of a schema

See also