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)
See also
Was this helpful?
