How do I view a created database and its character set as well as LC_COLLATE and LC_CTYPE information?
Use the following command to query the system table pg_database and obtain the created database and its character set as well as LC_COLLATE and LC_CTYPE information.
select datname,pg_encoding_to_char(encoding),datcollate,datctype from pg_database;
What should I do if an error is reported stating a mismatch between the character set and locale?
When you create a database using LC_COLLATE that does not match the character set, the following error will be reported.
How to resolve this issue:
1. Query the character set supported by the template library.
2. Query the LC_COLLATE information supported by the template library character set.
3. Modify to LC_COLLATE that matches the character set and re-execute the Create Database command.
How do I select collation when migrating from O to RDS-PostgreSQL?
To migrate from O to RDS-PostgreSQL, you should select the 'C' collation to obtain the expected results.
Creating a Database from the Command Line
Application Scenarios
After creating an instance, you can create a database, specify a template library, set different character sets, and localize collate and other attributes for the database according to your actual needs. This section describes how to connect to a database using the psql tool and create a database from the command line.
Grammatical Format
CREATE DATABASE name
[[WITH] [ OWNER [=] user_name ]
[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ STRATEGY [=] strategy ]
[ LOCALE [=] locale ]
[ LC_COLLATE [=] lc_collate ]
[ LC_CTYPE [=] lc_ctype ]
[ ICU_LOCALE [=] icu_locale ]
[ LOCALE_PROVIDER [=] locale_provider ]
[ COLLATION_VERSION [=] collation_version ]
[ TABLESPACE [=] tablespace_name ]
[ ALLOW_CONNECTIONS [=] allowconn ]
[ CONNECTION LIMIT [=] connlimit ]
[ IS_TEMPLATE [=] istemplate ]
[ OID [=] oid ]]
Operation Flow
1. Log in to the system and connect an instance.
2. Create a database and execute the following command, where dbname is the database name.
create database dbname;
3. The implementation result is shown in the figure below.
Character Set Type Options
Application Scenarios
Set the character set type when creating a database.
Procedure
You can specify the character set by using with encoding when creating a database.
An example is shown below, where dbname is the name of the database created.
create database dbname with encoding 'utf8';