CONVERT TO SCHEMA

On this page

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.

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.

Note:

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)

Pricing

Contact us

Sign In