Creating Databases ​

Physical databases have some important attributes, including their name, owner, and encoding. See CREATE DATABASE for syntax details.

Database Encodings ​

The database encoding is critical because this attribute determines the type of data that can be loaded and queried. If you want to load and query multi-byte data, you need to create a database with the UTF8 encoding. By default, databases are created with the LATIN9 encoding, which does not support multi-byte data but provides better performance for query processing. If your database will not contain any multibyte data, use LATIN9.

LATIN9, (also known as ISO 8859-15) is based on LATIN1 but has a few differences, including the addition of the Euro sign and replacements for some other characters. LATIN9 encodes 8-bit single-byte characters, covering character sets commonly used in Western Europe, the Americas, Oceania, and Africa.

You cannot change the encoding at runtime, so it is important to choose the right encoding when you create a database.

Note: Yellowbrick databases only support LATIN9 and UTF8 encodings in the database and for all data export (SELECT, ybsql \copy to, ybunload). For convenience, import paths via ybsql \copy from and ybload can read other encodings on the client side and transcode to one of the Yellowbrick-supported encodings.

• The Yellowbrick ybsql \copy from command supports the client encodings that Postgres supports. See the Postgres 9.5 documentation.
• Yellowbrick ybload operations support the client encodings that Java supports, but this list depends on the specific version of Java that is installed. For example, if you are using the default distribution of Oracle Java 8, see the Oracle documentation.

Locales ​

The locale for both LATIN9 databases and UTF8 databases is C. This locale applies to all database operations; no other locales are supported. You cannot change locale settings such as lc_time, lc_collate, and lc_numeric.

The bulk loader (ybload) supports different locales only for the purpose of importing data; once data is stored in a Yellowbrick database, it is always displayed with the default locale settings and sorted with the default collation order. See ybload Options for information about the --locale option.

Schemas ​

A database schema is a means of segregating tables and other objects inside a single database. For syntax details, see CREATE SCHEMA. After creating a schema, you can create tables and views that belong to it, as well as grant privileges to the objects in the schema.

The search_path configuration parameter defines the visibility of schemas to users.

premdb=# set search_path to 'bobr';
SET
premdb=# \d
       List of relations
 Schema | Name | Type  |  Owner  
--------+------+-------+---------
 bobr   | new  | table | brumsby
(1 row)
premdb=# show search_path;
 search_path 
-------------
 bobr
(1 row)

premdb=# reset search_path;
RESET
premdb=# show search_path;
   search_path   
-----------------
 "$user", public
(1 row)

Alternatively, you can use a SET SCHEMA command, with single quotes around the schema name:

premdb=# set schema 'bobr';
SET

Database Modes ​

The backup and restore and database replication features both make use of special modes that make databases available or unavailable for write operations. See HOT_STANDBY and READONLY Modes.

Cross-Database Queries ​

Yellowbrick databases are independent objects, but you can reference tables from multiple databases in the same query (in addition to writing queries that cross schemas in the same database). See Cross-Database Queries.

Parent topic:Database Administration