The CONVERT TO SCHEMA
statement converts a database to a new, user-defined schema. When you convert a database to a schema, all tables, sequences, and user-defined types in the database become child objects of the new schema, and the database is deleted.
The CONVERT TO SCHEMA
statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.
In CockroachDB versions < v20.2, user-defined schemas are not supported, and all stored objects in a given database use the public
schema. To provide a multi-level structure for stored objects in earlier versions of CockroachDB, we've recommended using database namespaces instead of schema namespaces. The CONVERT TO SCHEMA
statement is meant to help users who are upgrading to v20.2 and want to use schema namespaces in a way that is more similar to PostgreSQL.
CONVERT TO SCHEMA
is a subcommand of ALTER DATABASE
.
Required privileges
Only members of the admin
role can convert databases to schemas. By default, the root
user belongs to the admin
role.
Syntax
ALTER DATABASE <name> CONVERT TO SCHEMA WITH PARENT <parent_name>
Parameters
Parameter | Description |
---|---|
name |
The name of the database to convert. |
parent_name |
The name of the parent database to which the new schema will belong. |
Limitations
A database cannot be converted to a schema if:
- The database is the current database.
- The database has a child schema other than the
public
schema. - The database contains dependent objects (e.g., views).
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
Convert a database to a schema
By default, tables are stored in the public
schema:
> SHOW TABLES FROM public;
schema_name | table_name | type | estimated_row_count
--------------+----------------------------+-------+----------------------
public | promo_codes | table | 1000
public | rides | table | 500
public | user_promo_codes | table | 0
public | users | table | 50
public | vehicle_location_histories | table | 1000
public | vehicles | table | 15
(6 rows)
Suppose that you want to convert movr
to a schema, with a new database named cockroach_labs
as its parent.
First, create the new database:
> CREATE DATABASE cockroach_labs;
Then, set the new database as the current database (recall that you cannot convert the current database to a schema):
> USE cockroach_labs;
Convert the movr
database to a schema, with cockroach_labs
as its parent database:
> ALTER DATABASE movr CONVERT TO SCHEMA WITH PARENT cockroach_labs;
> SHOW SCHEMAS;
schema_name
----------------------
crdb_internal
information_schema
movr
pg_catalog
pg_extension
public
(6 rows)
> SHOW TABLES;
schema_name | table_name | type | estimated_row_count
--------------+----------------------------+-------+----------------------
movr | promo_codes | table | 0
movr | rides | table | 0
movr | user_promo_codes | table | 0
movr | users | table | 0
movr | vehicle_location_histories | table | 0
movr | vehicles | table | 0
(6 rows)
> SHOW TABLES FROM public;
schema_name | table_name | type | estimated_row_count
--------------+------------+------+----------------------
(0 rows)