The SET SCHEMA
statement changes the schema of a table.
SET SCHEMA
is a subcommand of ALTER TABLE
.
CockroachDB also supports SET SCHEMA
as an alias for setting the search_path
session variable.
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:
$ 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:
> 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:
> CREATE SCHEMA IF NOT EXISTS cockroach_labs;
Then, change the table's schema:
> ALTER TABLE promo_codes SET SCHEMA cockroach_labs;
> 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)