CREATE DATABASE ​

Create a new physical database with create-time encoding and locale plus additional connection properties.

CREATE DATABASE name
   [ [ WITH ] [ OWNER [=] user_name ]
          [ ENCODING [=] encoding ]
          [ LC_COLLATE [=] lc_collate ]
          [ LC_CTYPE [=] lc_ctype ]
          [ 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.

LC_COLLATE

Collation order to use in the new database. This affects the sort order applied to strings. For example, in queries with ORDER BY, the default is C. For UTF8 databases you typically want a value that matches your LC_CTYPE. See Usage Notes below for additional details.

LC_CTYPE

Character classification to use in the new database. This affects the categorization of characters. For example, lower, upper and digit. The default is C. In UTF8 databases, if you need case sensitive comparison of multi-byte characters, you want to explicitly set a locale. The default C encoding means that functions that rely on character classification will respect the ANSI C character classification. See Usage Notes below for additional details.

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.

Usage Notes ​

To create a database, you must be a superuser or have the special CREATEDB privilege. See CREATE USER.

You can set the LC_COLLATE and LC_CTYPE database attributes only at the time of creation and only via SQL.

• Once created, the encoding of a database cannot be changed.
• The SMC does not support setting the locale during database creation.
• If you have created a database with the default C encoding but need to change it, contact Yellowbrick Technical Support.

You can create a maximum of 1,000 databases per appliance. (The database yellowbrick is included in this number.). A very large number of databases is not recommended. Each database creates additional overhead and and is often an indicator that databases are being used in place of schemas which is not a best practice.

Because Yellowbrick supports cross database statements, care needs to be taken when mixing LATIN9 and UTF8 databases in a single statement to avoid "invalid UTF8 character" errors. In a cross database statement, the database from which the statement is executed determines the encoding that is used. Yellowbrick does not do automatic transcoding (For example, converting of high-bit ASCII to UTF8 characters and vice versa).

If you change the LC_COLLATE or LC_CTYPE for any database used in cross-database statements, you want to be consistent across all databases involved in those queries. Using different LC_COLLATE or LC_CTYPE locales can result in inconsistent behavior between databases and unexpected behavior from cross database statements.

Use locales only if you actually need them. Using locales other than the default of C for LC_COLLATE or LC_CTYPE does have a performance impact on character handling scenarios with ordering or case sensitive functions. The drawback of not using them is that case conversion and ordering of multi-byte characters may not occur. For example, when using the default of locale of C with Danish UTF8 characters, LOWER(Å) returns Å , not å.

The valid locales for LC_COLLATE and LC_CTYPE are the supported locales on the Yellowbrick manager node. You can list them using the Linux command locale -a. A number of behaviours follow the locale settings. In particular, the locale affects:

• Case insensitive matching and the classification of characters by character-class regular expressions. For example, pattern matching operators including LIKE, SIMILAR TO, and POSIX-style regular expressions.
• Sort order in queries containing an ORDER BY clause or the standard comparison operators on textual data.
• The UPPER, LOWER, and INITCAP functions.
• The TO_CHAR family of functions.

All other comparisons including joining, grouping, and aggregates use a simple binary comparison.

Examples ​

Create a database with the default LATIN9 encoding specifying the owner.

yellowbrick=# create database mydb with owner=yb_dbo;

Create a UTF8 database with an explicit collation and ctype.

yellowbrick=# create database mydb with encoding=utf8 lc_ctype='en_US.UTF-8' lc_collate='en_US.UTF-8' ;

Create a database that does not allow connections to it.

yellowbrick=# create database mydb allow_connections=false;

Create a database with a maximum limit of 100 concurrent connections.

yellowbrick=# create database mydb connection limit 100;

Create a database in hot_standby mode.

yellowbrick=# create database premdb_hot with hot_standby on;

You cannot create a database with an encoding other than LATIN9 or UTF8.

yellowbrick=# create database shakespeare encoding=LATIN1;
ERROR: Only LATIN9 and UTF8 database encodings are supported