SET SCHEMA

The SET SCHEMA statement changes the schema of a table.

Note:

SET SCHEMA is a subcommand of ALTER TABLE.

CockroachDB also supports SET SCHEMA as an alias for setting the search_path session variable.

Note:

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

Required privileges

The user must have the DROP privilege on the table, and the CREATE privilege on the schema.

Syntax

Tables

ALTER TABLE [IF EXISTS] <name> SET SCHEMA <newschemaname>

Parameters

Parameter Description
name The name of the table to alter.
newschemaname The name of the table's new schema.

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

Change the schema of a table

Suppose you want to add the promo_codes table to a new schema called cockroach_labs.

By default, unqualified tables created in the database belong to the public schema:

icon/buttons/copy
> SHOW TABLES;
  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)

If the new schema does not already exist, create it:

icon/buttons/copy
> CREATE SCHEMA IF NOT EXISTS cockroach_labs;

Then, change the table's schema:

icon/buttons/copy
> ALTER TABLE promo_codes SET SCHEMA cockroach_labs;
icon/buttons/copy
> SHOW TABLES;
   schema_name   |         table_name         | type  | estimated_row_count
-----------------+----------------------------+-------+----------------------
  cockroach_labs | 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)

See also


Yes No