Help Center/ Relational Database Service/ Best Practices/ RDS for PostgreSQL/ Creating Databases/ FAQ/ How Do I Create a Database Using a Command-Line Interface (CLI)?
Updated on 2024-11-21 GMT+08:00
View PDF
Share

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

When creating databases, you can specify a template database and set different character sets and collations for each database.

You can connect to your instance using the DAS query window, psql, or pgadmin. This section takes the DAS query window as an example to describe how to create a database using the CREATE DATABASE command.

Collation information includes LC_COLLATE and LC_CTYPE. For details, see the official documentation.

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.
  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.
Parent Topic: FAQ