SET SCHEMA

Warning:
Cockroach Labs will stop providing Assistance Support for this version on November 18, 2022. Prior to that date, upgrade to a more recent version to continue receiving support. For more details, see the Release Support Policy.

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.

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