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)