Help Center/ GaussDB(DWS)/ Standard Data Warehouse (8.1.3.x)/ Defining Database Objects/ Creating and Managing Databases
Updated on 2023-10-31 GMT+08:00
View PDF

Creating and Managing Databases

A database is a collection of objects such as tables, indexes, views, stored procedures, and operators. GaussDB (DWS) supports the creation of multiple databases. However, a client program can connect to and access only one database at a time, and cross-database query is not supported.

Template and Default Databases

  • GaussDB (DWS) provides two template databases template0 and template1 and a default database gaussdb.
  • By default, each newly created database is based on a template database. The GaussDB(DWS) database uses template1 as the template by default. The encoding format is SQL_ASCII, and user-defined character encoding is not allowed. If you need to specify the character encoding when creating a database, use template0 to create the database.
  • Do not use a client or any other tools to connect to or to perform operations on both the two template databases.

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

Creating a Database.

Run the CREATE DATABASE command to create a database.

Only the database system administrator or users granted with database creation permissions can create a database. For details about how to grant database creation permissions to a user, see Managing Users and Their Permissions.

  • When you create a database, if the length of the database name exceeds 63 bytes, the server truncates the database name and retains the first 63 bytes. Therefore, you are advised to set the length of the database name to a value less than or equal to 63 bytes. Do not use multi-byte characters as object names. 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.
  • Database names must comply with the naming convention of SQL identifiers. The current user automatically becomes the owner of this new database.
  • If a database system is used to support independent users and projects, 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 maximum of 128 databases can be created in GaussDB(DWS).

Viewing Databases

To view databases, perform the following steps:
  • Run the \l meta-command to view the database list of the database system.
  • Run the following command to query the database list in the pg_database system catalog:

Modifying a Database

You can modify database configuration such as the database owner, name, and default settings.

  • Run the following command to set the default search path for the database:
  • Run the following command to rename the database:

Deleting a Database

You can run DROP DATABASE statement to delete a database. This command deletes the system catalog of the database and the database directory on the disk. Only the database owner or system administrator can delete a database. A database being accessed by users cannot be deleted, You need to connect to another database before deleting this database.

Run the following command to delete a database: