CREATE DATABASE

On this page

The CREATE DATABASE statement creates a new CockroachDB database.

Note:

This statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.

Required privileges

To create a database, the user must be a member of the admin role or must have the CREATEDB parameter set.

Synopsis

Parameters

Parameter	Description
`IF NOT EXISTS`	Create a new database only if a database of the same name does not already exist; if one does exist, do not return an error.
`name`	The name of the database to create, which must be unique and follow these identifier rules.
`encoding`	The `CREATE DATABASE` statement accepts an optional `ENCODING` clause for compatibility with PostgreSQL, but `UTF-8` is the only supported encoding. The aliases `UTF8` and `UNICODE` are also accepted. Values should be enclosed in single quotes and are case-insensitive. Example: `CREATE DATABASE bank ENCODING = 'UTF-8'`.
`CONNECTION LIMIT`	Supported for compatibility with PostgreSQL. A value of `-1` indicates no connection limit. Values other than `-1` are currently not supported. By default, `CONNECTION LIMIT = -1`.
`PRIMARY REGION region_name`	New in v21.1: Create a multi-region database with `region_name` as the primary region. Allowed values include any region returned by `SHOW REGIONS FROM CLUSTER`.
`REGIONS region_name_list`	New in v21.1: Create a multi-region database with `region_name_list` as database regions. Allowed values include any region returned by `SHOW REGIONS FROM CLUSTER`. To set database regions at database creation, a primary region must be specified in the same `CREATE DATABASE` statement.
`SURVIVE ZONE FAILURE` (Default) `SURVIVE REGION FAILURE`	New in v21.1: Create a multi-region database with regional failure or zone failure survival goals. To set the regional failure survival goal, the database must have at least 3 database regions. Surviving zone failures is the default setting for multi-region databases.

Example

Create a database

> CREATE DATABASE bank;

CREATE DATABASE

> SHOW DATABASES;

  database_name | owner | primary_region | regions | survival_goal
----------------+-------+----------------+---------+----------------
  bank          | demo  | NULL           | {}      | NULL
  defaultdb     | root  | NULL           | {}      | NULL
  postgres      | root  | NULL           | {}      | NULL
  system        | node  | NULL           | {}      | NULL
(4 rows)

Create fails (name already in use)

> CREATE DATABASE bank;

ERROR: database "bank" already exists
SQLSTATE: 42P04

> CREATE DATABASE IF NOT EXISTS bank;

CREATE DATABASE

SQL does not generate an error, but instead responds CREATE DATABASE even though a new database wasn't created.

> SHOW DATABASES;

  database_name | owner | primary_region | regions | survival_goal
----------------+-------+----------------+---------+----------------
  bank          | demo  | NULL           | {}      | NULL
  defaultdb     | root  | NULL           | {}      | NULL
  postgres      | root  | NULL           | {}      | NULL
  system        | node  | NULL           | {}      | NULL
(4 rows)

Create a multi-region database

Note:

This is an enterprise-only feature. You can use free trial credits to try it out.

Suppose you start a cluster with region and zone localities specified at startup.

For this example, let's use a demo cluster, with the --demo-locality flag to simulate a multi-region cluster:

cockroach211 demo --nodes=6 --demo-locality=region=us-east1,zone=us-east1-a:region=us-east1,zone=us-east1-b:region=us-central1,zone=us-central1-a:region=us-central1,zone=us-central1-b:region=us-west1,zone=us-west1-a:region=us-west1,zone=us-west1-b --no-example-database

> SHOW REGIONS;

    region    |             zones             | database_names | primary_region_of
--------------+-------------------------------+----------------+--------------------
  us-central1 | {us-central1-a,us-central1-b} | {}             | {}
  us-east1    | {us-east1-a,us-east1-b}       | {}             | {}
  us-west1    | {us-west1-a,us-west1-b}       | {}             | {}
(3 rows)

If regions are set at cluster start-up, you can create multi-region databases in the cluster that use the cluster regions.

Use the following command to specify regions and survival goals at database creation:

> CREATE DATABASE bank PRIMARY REGION "us-east1" REGIONS "us-east1", "us-central1", "us-west1" SURVIVE REGION FAILURE;

> SHOW DATABASES;

  database_name | owner | primary_region |             regions             | survival_goal
----------------+-------+----------------+---------------------------------+----------------
  bank          | demo  | us-east1       | {us-central1,us-east1,us-west1} | region
  defaultdb     | root  | NULL           | {}                              | NULL
  postgres      | root  | NULL           | {}                              | NULL
  system        | node  | NULL           | {}                              | NULL
(4 rows)

> SHOW REGIONS FROM DATABASE bank;

  database |   region    | primary |             zones
-----------+-------------+---------+--------------------------------
  bank     | us-east1    |  true   | {us-east1-a,us-east1-b}
  bank     | us-central1 |  false  | {us-central1-a,us-central1-b}
  bank     | us-west1    |  false  | {us-west1-a,us-west1-b}
(3 rows)

Pricing

Contact us

Sign In