How Do I Create a Database Using a Command-Line Interface (CLI)?

Syntax

CREATE DATABASE name    
[ [ WITH ] [ OWNER [=] user_name ]           
       [ TEMPLATE [=] template ]           
       [ ENCODING [=] encoding ]           
       [ LC_COLLATE [=] lc_collate ]           
       [ LC_CTYPE [=] lc_ctype ]           
       [ TABLESPACE [=] tablespace_name ]          
       [ ALLOW_CONNECTIONS [=] allowconn ]           
       [ CONNECTION LIMIT [=] connlimit ]          
       [ IS_TEMPLATE [=] istemplate ] ]

Procedure

1. Log in to the management console.
2. Click in the upper left corner and select a region and a project.
3. Click in the upper left corner of the page and choose Databases > Relational Database Service.
4. On the Instances page, locate the target DB instance and click Log In in the Operation column.
   Figure 1 Logging in to an instance
   

5. On the displayed page, enter the username and password and click Log In.
6. Choose SQL Operations > SQL Window.
7. On the displayed DAS console, choose SQL Operations > SQL Query. Run the following command to create a database:

   create database Database name;

Parameter Description

• TEMPLATE

  RDS for PostgreSQL has two database templates: template0 and template1. The default template is template1. When you use template1 to create a database, do not specify a new character set for the database. Otherwise, an error will be reported. You can also specify a custom template to create a database.

• ENCODING

  When creating a database, you can specify a character set using WITH ENCODING. For details about the supported character sets, see the official documentation.

• LC_COLLATE

  String sort order. The default value is en_US.utf8.

  Comparison of the same string in different collations may have different results.

  For example, after you execute SELECT 'a'>'A';, the result is false if this parameter is set to en_US.utf8 and the result is true if this parameter is set to C. If you need to migrate a database from Oracle to RDS for PostgreSQL, set LC_COLLATE to C. You can query the supported collations from the pg_collation table.

• LC_CTYPE

  It is used to classify if a character is a digit, uppercase letter, lowercase letter, and so on. You can query the supported character classifications from the pg_collation table.

• For details about other parameters, see the official documentation.

How to Use These Parameters

• Using TEMPLATE to specify a database template
  • When template1 is used, the character set or collation defined in this template cannot be changed. For details about collations, see Configuring the Collation of a Database in a Locale.

    CREATE DATABASE my_db WITH TEMPLATE template1 ;

  • When template0 is used, you can change the character set and collation. For details, see Configuring the Collation of a Database in a Locale.

    CREATE DATABASE my_db WITH ENCODING = 'UTF8' LC_COLLATE ='zh_CN.utf8' LC_CTYPE ='zh_CN.utf8' TEMPLATE = template0 ;

  • If no template is specified during database creation, template1 is used by default. You can also specify a custom template to create a database.

    CREATE DATABASE my_db WITH TEMPLATE = mytemplate;

• Using WITH ENCODING to specify a character set

  CREATE DATABASE my_db WITH ENCODING 'UTF8';

• LC_COLLATE and LC_CTYPE
  • Querying character sets (encodings) supported by LC_COLLATE and LC_CTYPE

    SELECT pg_encoding_to_char(collencoding) AS encoding,collname,collcollate AS "LC_COLLATE",collctype AS "LC_CTYPE" FROM pg_collation;

    If encoding is empty, LC_COLLATE supports all character sets.

    

  • Configuring the collation of a database in a locale

    Run the following command to create a database with LC_COLLATE and LC_CTYPE set to zh_CN.utf8:

    CREATE DATABASE my_db WITH ENCODING = 'UTF8' LC_COLLATE ='zh_CN.utf8' LC_CTYPE ='zh_CN.utf8' TEMPLATE = template0 ;

    

    If the specified LC_COLLATE is incompatible with the character set, error information similar to the following is displayed:

    

    

    1. The specified LC_COLLATE and LC_CTYPE must be compatible with the target character set. Otherwise, an error is reported. For details, see Querying LC_COLLATE and LC_CTYPE Settings Supported by a Character Set.
    2. The LC_COLLATE and LC_CTYPE settings of an existing database cannot be changed by running the ALTER DATABASE statement. You can change them while creating a new database and then import your data to the new database.