CREATE SCHEMA

On this page Carat arrow pointing down

The CREATE SCHEMA statement creates a user-defined schema.

Note:

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

CREATE SCHEMA IF NOT EXISTS name . name name . name AUTHORIZATION role_spec

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:

icon/buttons/copy
cockroach demo

Create a schema

icon/buttons/copy
CREATE SCHEMA org_one;
icon/buttons/copy
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

icon/buttons/copy
CREATE SCHEMA org_one;
ERROR: schema "org_one" already exists
icon/buttons/copy
CREATE SCHEMA IF NOT EXISTS org_one;

SQL does not generate an error, even though a new schema wasn't created.

icon/buttons/copy
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.

icon/buttons/copy
CREATE SCHEMA IF NOT EXISTS org_one;
icon/buttons/copy
CREATE SCHEMA IF NOT EXISTS org_two;
icon/buttons/copy
SHOW SCHEMAS;
     schema_name
----------------------
  crdb_internal
  information_schema
  org_one
  org_two
  pg_catalog
  pg_extension
  public
(7 rows)
icon/buttons/copy
CREATE TABLE org_one.employees (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        name STRING,
        desk_no INT UNIQUE
);
icon/buttons/copy
CREATE TABLE org_two.employees (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        name STRING,
        desk_no INT UNIQUE
);
icon/buttons/copy
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:

icon/buttons/copy
CREATE USER max WITH PASSWORD 'roach';
icon/buttons/copy
CREATE SCHEMA org_two AUTHORIZATION max;
icon/buttons/copy
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:

icon/buttons/copy
CREATE SCHEMA AUTHORIZATION max;
icon/buttons/copy
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:

icon/buttons/copy
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:

icon/buttons/copy
cockroach sql --url 'postgres://max:roach@host:port/db?sslmode=require'
icon/buttons/copy
CREATE TABLE max.accounts (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        name STRING,
        balance DECIMAL
);
icon/buttons/copy
CREATE TABLE public.accounts (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        name STRING,
        balance DECIMAL
);
icon/buttons/copy
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:

icon/buttons/copy
INSERT INTO accounts (name, balance) VALUES ('checking', 1000), ('savings', 15000);
icon/buttons/copy
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.

icon/buttons/copy
SELECT * FROM public.accounts;
  id | name | balance
-----+------+----------
(0 rows)

See also


Yes No
On this page

Yes No