> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# CREATE SCHEMA

The `CREATE SCHEMA` [statement](/docs/stable/sql-statements.md) creates a user-defined [schema](/docs/stable/sql-name-resolution.md#naming-hierarchy).

> [!NOTE]
>
> The `CREATE SCHEMA` statement performs a schema change. For more information about how online schema changes work in CockroachDB, see [Online Schema Changes](/docs/stable/online-schema-changes.md).

## 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](/docs/stable/grant.md) 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

![create\_schema syntax diagram](https://mintcdn.com/cockroachlabs/UZNSFbmU2EwEm6G5/images/sql-diagrams/common/create_schema.svg?fit=max\&auto=format\&n=UZNSFbmU2EwEm6G5\&q=85\&s=13fe0c5533e210cd67444be4242e928d)

### 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](/docs/stable/keywords-and-identifiers.md#identifiers). |
| `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`](/docs/stable/cockroach-demo) to start a temporary, in-memory cluster with the [`movr`](/docs/stable/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;
```

```
     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;
```

```
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;
```

```
     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;
```

```
     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';
```

```
  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;
```

```
     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;
```

```
     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](/docs/stable/sql-name-resolution.md#search-path), 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](/docs/stable/grant) (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](/docs/stable/cockroach-sql.md) 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;
```

```
  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;
```

```
                   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;
```

```
  id | name | balance
-----+------+----------
(0 rows)
```

## See also

* [`SHOW SCHEMAS`](/docs/stable/show-schemas)
* [`SET SCHEMA`](/docs/stable/alter-table#set-schema)
* [`DROP SCHEMA`](/docs/stable/drop-schema)
* [`ALTER SCHEMA`](/docs/stable/alter-schema)
* [SQL Statements](/docs/stable/sql-statements.md)
* [Online Schema Changes](/docs/stable/online-schema-changes.md)