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.
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)
