Product Documentation

Appearance

CREATE DATABASE ​

Create a new physical database.

You can create a maximum of 1,000 databases per instance. (The yellowbrick database is included in this number.)

CREATE DATABASE name
   [ [ WITH ] [ OWNER [=] user_name ]
          [ ENCODING [=] encoding ]
          [ ALLOW_CONNECTIONS [=] { true | false } ] ]
          [ CONNECTION LIMIT number ]
          [ HOT_STANDBY ON ]
name

Database name. Names longer than 128 characters are allowed but truncated. See also SQL Identifiers. Space characters are not allowed in database names.

Note: You can also use Yellowbrick Manager to create databases. When you create a database in Yellowbrick Manager, the database name is implicitly quoted and the case of the name is preserved. In ybsql, you need to use an explicit quoted identifier to create a database with a case-sensitive name.

OWNER

Define the user who owns the database.

ENCODING

Define the database encoding. Only the LATIN9 and UTF8 encodings are supported. The default encoding is LATIN9. For multi-byte character sets, use the UTF8 encoding. If your data is all single-byte, use the LATIN9 encoding, which provides better query performance.

Note: To load UTF-16 data with ybload, create a UTF8 database and set the ybload --encoding option to UTF16. See ybload Options.

ALLOW_CONNECTIONS

Specify whether the database accepts user connections: true or false.

CONNECTION LIMIT

Set a limit on the number of concurrent database connections by regular users (superuser connections do not count). Some of these connections may be used up by internal database services.

HOT_STANDBY ON

Create a database that is ready for use as a target for restore operations or replication.

Note: You can run queries that reference tables in different databases. See Cross-Database Queries.

Examples ​

For example:

premdb=# create database testdb encoding=latin9;
CREATE DATABASE
premdb=# create database mydb allow_connections=false;
CREATE DATABASE
premdb=# create database premdb connection limit 100;
CREATE DATABASE
premdb=# create database shakespeare with owner=ybd encoding=LATIN1;
ERROR:  Only LATIN9 and UTF8 database encodings are supported
yellowbrick=# create database premdb_hot with hot_standby on;
CREATE DATABASE

Parent topic:SQL Commands