> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# CREATE SCHEMA
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
The `CREATE SCHEMA` statement creates a user-defined schema.
The `CREATE SCHEMA` statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.
## Required privileges
* Only members of the `admin` role can create new schemas. By default, the `root` user belongs to the `admin` 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
To follow along, run `cockroach demo` to start a temporary, in-memory cluster with the `movr` sample dataset preloaded:
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach demo
```
### Create a schema
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE SCHEMA org_one;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW SCHEMAS;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE SCHEMA org_one;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ERROR: schema "org_one" already exists
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE SCHEMA IF NOT EXISTS org_one;
```
SQL does not generate an error, even though a new schema wasn't created.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW SCHEMAS;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE SCHEMA IF NOT EXISTS org_one;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE SCHEMA IF NOT EXISTS org_two;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW SCHEMAS;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
schema_name
----------------------
crdb_internal
information_schema
org_one
org_two
pg_catalog
pg_extension
public
(7 rows)
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE org_one.employees (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name STRING,
desk_no INT UNIQUE
);
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE org_two.employees (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name STRING,
desk_no INT UNIQUE
);
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> WITH x as (SHOW TABLES) SELECT * x WHERE table_name='employees';
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE USER max WITH PASSWORD 'roach';
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE SCHEMA org_two AUTHORIZATION max;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW SCHEMAS;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE SCHEMA AUTHORIZATION max;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW SCHEMAS;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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:
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach sql --url 'postgres://max:roach@host:port/db?sslmode=require'
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE max.accounts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name STRING,
balance DECIMAL
);
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE public.accounts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name STRING,
balance DECIMAL
);
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW TABLES;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO accounts (name, balance) VALUES ('checking', 1000), ('savings', 15000);
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM accounts;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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`.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM public.accounts;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
id | name | balance
-----+------+----------
(0 rows)
```
## See also
* `SHOW SCHEMAS`
* `SET SCHEMA`
* `DROP SCHEMA`
* `ALTER SCHEMA`
* SQL Statements
* Online Schema Changes