ALTER SCHEMA

On this page Carat arrow pointing down
Warning:
As of June 5, 2024, CockroachDB v22.2 is no longer supported. For more details, refer to the Release Support Policy.

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

Note:

The ALTER SCHEMA statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.

Syntax

ALTER SCHEMA name . name RENAME TO schema_new_name OWNER TO role_spec

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

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:

icon/buttons/copy
$ cockroach demo

Rename a schema

Note:

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:

icon/buttons/copy
CREATE USER max;
icon/buttons/copy
CREATE SCHEMA org_one AUTHORIZATION max;
icon/buttons/copy
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:

icon/buttons/copy
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.

icon/buttons/copy
DROP SCHEMA org_one;
icon/buttons/copy
CREATE SCHEMA org_one;

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

icon/buttons/copy
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:

icon/buttons/copy
ALTER SCHEMA org_one RENAME TO org_two;
icon/buttons/copy
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:

icon/buttons/copy
CREATE SCHEMA org_one;
icon/buttons/copy
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.

icon/buttons/copy
GRANT max TO root;
icon/buttons/copy
GRANT CREATE ON DATABASE defaultdb TO max;
icon/buttons/copy
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:

icon/buttons/copy
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


Yes No
On this page

Yes No