The CREATE SCHEMA
statement creates a user-defined schema.
You can also create a user-defined schema by converting an existing database to a schema using ALTER DATABASE ... CONVERT TO SCHEMA
.
Required privileges
- Only members of the
admin
role can create new schemas. By default, theroot
user belongs to theadmin
role. - To grant privileges on a user-defined schema, a user must have the
GRANT
privilege on the schema and the privilege that they want to grant. - To create or interact with objects that depend on a user-defined schema, a user must have the
USAGE
privilege on the schema.
Syntax
Parameters
Parameter | Description |
---|---|
IF NOT EXISTS |
Create a new schema only if a schema of the same name does not already exist within the database. If one does exist, do not return an error. |
name name.name |
The name of the schema to create, or the name of the database in which to create the schema and the schema name, separated by a ". ". The schema name must be unique within its database and follow these identifier rules. |
AUTHORIZATION role_spec |
Optionally identify a user (role_spec ) to be the owner of the schema.If a CREATE SCHEMA statement has an AUTHORIZATION clause, but no schema name is specified, the schema will be named after the specified owner of the schema. If a CREATE SCHEMA statement does not have an AUTHORIZATION clause, the user executing the statement will be named the owner. |
Example
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
Create a schema
> CREATE SCHEMA org_one;
> SHOW SCHEMAS;
schema_name
----------------------
crdb_internal
information_schema
org_one
pg_catalog
pg_extension
public
(6 rows)
By default, the user executing the CREATE SCHEMA
statement is the owner of the schema. For example, suppose you created the schema as user root
. root
would be the owner of the schema.
Create a schema if one does not exist
> CREATE SCHEMA org_one;
ERROR: schema "org_one" already exists
> CREATE SCHEMA IF NOT EXISTS org_one;
SQL does not generate an error, even though a new schema wasn't created.
> SHOW SCHEMAS;
schema_name
----------------------
crdb_internal
information_schema
org_one
pg_catalog
pg_extension
public
(6 rows)
Create two tables of the same name in different schemas
You can create tables of the same name in the same database if they are in separate schemas.
> CREATE SCHEMA IF NOT EXISTS org_one;
> CREATE SCHEMA IF NOT EXISTS org_two;
> SHOW SCHEMAS;
schema_name
----------------------
crdb_internal
information_schema
org_one
org_two
pg_catalog
pg_extension
public
(7 rows)
> CREATE TABLE org_one.employees (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name STRING,
desk_no INT UNIQUE
);
> CREATE TABLE org_two.employees (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name STRING,
desk_no INT UNIQUE
);
> SELECT * FROM [SHOW TABLES] WHERE table_name='employees';
schema_name | table_name | type | estimated_row_count
--------------+------------+-------+----------------------
org_one | employees | table | 0
org_two | employees | table | 0
(2 rows)
Create a schema with authorization
To specify the owner of a schema, add an AUTHORIZATION
clause to the CREATE SCHEMA
statement:
> CREATE USER max WITH PASSWORD 'roach';
> CREATE SCHEMA org_two AUTHORIZATION max;
> SHOW SCHEMAS;
schema_name
----------------------
crdb_internal
information_schema
org_two
pg_catalog
pg_extension
public
(6 rows)
If no schema name is specified in a CREATE SCHEMA
statement with an AUTHORIZATION
clause, the schema will be named after the user specified:
> CREATE SCHEMA AUTHORIZATION max;
> SHOW SCHEMAS;
schema_name
----------------------
crdb_internal
information_schema
max
org_two
pg_catalog
pg_extension
public
(7 rows)
When you use a table without specifying a schema, CockroachDB looks for the table in the $user
schema (i.e., a schema named after the current user). If no schema exists with the name of the current user, the public
schema is used.
For example, suppose that you grant the root
role (i.e., the role of the current user root
) to the max
user:
> GRANT root TO max;
Then, max
accesses the cluster and creates two tables of the same name, in the same database, one in the max
schema, and one in the public
schema:
$ cockroach sql --url 'postgres://max:roach@host:port/db?sslmode=require'
> CREATE TABLE max.accounts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name STRING,
balance DECIMAL
);
> CREATE TABLE public.accounts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name STRING,
balance DECIMAL
);
> SHOW TABLES;
schema_name | table_name | type | estimated_row_count
--------------+------------+-------+----------------------
max | accounts | table | 0
public | accounts | table | 0
(2 rows)
max
then inserts some values into the accounts
table, without specifying a schema:
> INSERT INTO accounts (name, balance) VALUES ('checking', 1000), ('savings', 15000);
> SELECT * FROM accounts;
id | name | balance
---------------------------------------+----------+----------
7610607e-4928-44fb-9f4e-7ae6d6520666 | savings | 15000
860b7891-cde4-4aff-a318-f928d47374bc | checking | 1000
(2 rows)
Because max
is the current user, all unqualified accounts
table names resolve as max.accounts
, and not public.accounts
.
> SELECT * FROM public.accounts;
id | name | balance
-----+------+----------
(0 rows)