Creating and Managing Databases

Prerequisites

To create a database, you must be a database system administrator or have the permission for creating databases. For details, see Users.

Background

• GaussDB(DWS) has two initial template databases template0 and template1 and a default user database gaussdb.
• CREATE DATABASE creates a database by copying a template database (template1 by default). Do not use a client or any other tools to connect to or to perform operations on the template databases.
• A maximum of 128 databases can be created in GaussDB(DWS).
• A database system consists of multiple databases. A client can connect to only one database at a time. You are not allowed to query data across databases. If a database cluster contains multiple databases, set the -d parameter to specify the database to connect to.

Procedure

1. Create database db_tpcds.

   1	CREATE DATABASE db_tpcds;

   If the following information is displayed, the database is created.

   1	CREATE DATABASE

   As stated in Background, the template database template1 is copied by default to create a database. Its encoding format is SQL_ASCII. If the name of an object created in this database contains multiple-byte characters (such as Chinese characters) and exceeds the name length limit (63 bytes), the system truncates the name from the last byte instead of the last character. As a result, characters may be incomplete.

   To solve the problem, the data object name should not exceed the maximum length or contain multi-byte characters.

   If an object whose name is truncated mistakenly cannot be deleted, delete the object using the name before the truncation, or manually delete it from the corresponding system catalog on each node.

   You can also use template0 to create a database by using CREATE DATABASE and specify new encoding and locale, for example, use UTF-8 as the default database encoding (server_encoding). For details, see the syntax of CREATE DATABASE.

   You can run the show server_encoding command to view the current database encoding.

   

   • Database names must comply with the general naming convention of SQL identifiers. The current user automatically becomes the owner of this new database.
   • If a database system supports independent users and projects, you are advised to store them in different databases.
   • If the projects or users are associated with each other and share resources, store them in different schemas in the same database. A schema is only a logical structure. For details about user permissions for schemas, see table 1 in Separation of Permissions.

2. View databases.
   • Query the database list using the \l meta-command.

     1

     \l

   • Query the database list in the system catalog pg_database:

     1	SELECT datname FROM pg_database;

3. Modify a database.

   You can run the ALTER DATABASE statement to modify database attributes, such as the owner, name, and default configuration attributes.

   • Run the following statement to specify the default schema search path:

     1	ALTER DATABASE db_tpcds SET search_path TO pa_catalog,public;

   • Run the following statement to rename the database:

     1	ALTER DATABASE db_tpcds RENAME TO human_tpcds;

4. Delete a database.

   You can run the DROP DATABASE statement to delete a database. This command deletes the system directory in the database, as well as the database directory on the disk that stores data. Only the database owner or the system administrator can delete a database. A database accessed by users cannot be deleted. You need to connect to another database before deleting this database.

   Run the following statement to delete the database:

   1	DROP DATABASE human_tpcds;