CREATE DATABASE

On this page Carat arrow pointing down
Warning:
CockroachDB v22.1 is no longer supported as of November 24, 2023. For more details, refer to the Release Support Policy.

The CREATE DATABASE statement creates a new CockroachDB database.

Note:

The CREATE DATABASE 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

CREATE DATABASE IF NOT EXISTS database_name WITH opt_template_clause ENCODING = encoding opt_lc_collate_clause opt_lc_ctype_clause CONNECTION LIMIT = limit PRIMARY REGION = region_name REGIONS = region_name_list SURVIVE = REGION ZONE FAILURE opt_owner_clause

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 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 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
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.

* New in v22.1: To control the maximum number of non-superuser (root user or other admin role) connections a gateway node can have open at one time, use the server.max_connections_per_gateway cluster setting. If a new non-superuser connection would exceed this limit, the error message "sorry, too many clients already" is returned, along with error code 53300. This setting may be useful until the CONNECTION LIMIT syntax is fully supported.

Example

Create a database

icon/buttons/copy
> CREATE DATABASE bank;
CREATE DATABASE
icon/buttons/copy
> 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)

icon/buttons/copy
> CREATE DATABASE bank;
ERROR: database "bank" already exists
SQLSTATE: 42P04
icon/buttons/copy
> 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.

icon/buttons/copy
> 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. Request a 30-day trial license 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:

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

icon/buttons/copy
> CREATE DATABASE bank PRIMARY REGION "us-east1" REGIONS "us-east1", "us-central1", "us-west1" SURVIVE REGION FAILURE;
icon/buttons/copy
> 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)
icon/buttons/copy
> 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)

See also


Yes No
On this page

Yes No