:Out-of-order Chinese sorting in PostgreSQL database

Problem description

The Chinese sorting in PostgreSQL database is out of order as follows:

• The Chinese characters in the PostgreSQL database are sorted out of order and are not sorted by normal pinyin.
• After an update statement is executed in the database, the data is added to the last position of the data table.

Solution

Alibaba Cloud reminds you that:

• Before you perform operations that may cause risks, such as modifying instance configurations or data, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
• You can modify the configurations and data of instances including but not limited to Elastic Compute Service (ECS) and Relational Database Service (RDS) instances. Before the modification, we recommend that you create snapshots or enable RDS log backup.
• If you have authorized or submitted sensitive information such as the logon account and password in the Alibaba Cloud Management Console, we recommend that you modify such information in a timely manner.

If the collation is not specified in the SQL statement, the SQL statement needs to be sorted by the UTF-8 character set. Modify the statement as follows:

select * From [$Table] order by NMAE collate "zh_CN.utf8";  

Note:

• We recommend that you do not directly use the default sorting provided by the client.
• [$Table] is the name of the data Table.

Scope

• ApsaraDB RDS for PostgreSQL