すべてのプロダクト
Search
ドキュメントセンター

ApsaraDB RDS:データベースの照合順序の設定

最終更新日:Jan 17, 2024

ApsaraDB RDS for PostgreSQLインスタンスを初期化するときに、ビジネス要件に基づいて各データベースの照合順序を設定できます。 照合順序には、文字列のソート順、文字の分類方法、数値形式、日時形式、および通貨形式が含まれます。 さらに、LC_COLLATEおよびLC_CTYPE環境変数を設定する必要がある場合もあります。

LC_COLLATE

文字列ソート順序

LC_CTYPE

キャラクター分類

LC_メッセージ

メッセージ言语

LC_MONETARY

通貨形式

LC_NUMERIC

数値値の形式

LC_TIME

日付と時刻の形式

これらの環境変数を構成して、ロケールでビジネス要件を満たす照合順序を指定できます。 このトピックでは、ロケールでデータベースの照合順序を設定する方法と、特定の順序で出力を並べ替える方法の例を示します。

サポートされている文字セット

詳細については、「文字セットのサポート」をご参照ください。

文字セットでサポートされているLC_COLLATEおよびLC_CTYPEの設定

次のSQL文を実行して、pg_collationシステムテーブルの文字セットでサポートされているLC_COLLATEおよびLC_CTYPE設定を照会できます。

pg_collationからpg_encoding_to_char(collencoding) をencoding、collname、collcollate、collctypeとして選択します。

照合順序のエンコーディングフィールドが空の場合、照合順序はすべての文字セットをサポートします。

エンコード | collcollate | collctype
------------ ----------------------- ----------------------- -----------------------
            | default | |
            | C | C | C
            | POSIX | POSIX | POSIX
 UTF8 | aa_DJ | aa_DJ.utf8 | aa_DJ.utf8
 LATIN1 | aa_DJ | aa_DJ | aa_DJ
 LATIN1 | aa_DJ.iso88591 | aa_DJ.iso88591 | aa_DJ.iso88591
 UTF8 | aa_DJ.utf8 | aa_DJ.utf8 | aa_DJ.utf8
 UTF8 | aa_ER | aa_ER | aa_ER
 UTF8 | aa_ER.utf8 | aa_ER.utf8 | aa_ER.utf8
......
 EUC_CN | zh_CN | zh_CN | zh_CN
 UTF8 | zh_CN | zh_CN.utf8 | zh_CN.utf8
 EUC_CN | zh_CN.gb2312 | zh_CN.gb2312 | zh_CN.gb2312
 UTF8 | zh_CN.utf8 | zh_CN.utf8 | zh_CN.utf8
 UTF8 | zh_HK | zh_HK.utf8 | zh_HK.utf8
 UTF8 | zh_HK.utf8 | zh_HK.utf8 | zh_HK.utf8
 EUC_CN | zh_SG | zh_SG | zh_SG
 UTF8 | zh_SG | zh_SG.utf8 | zh_SG.utf8
 EUC_CN | zh_SG.gb2312 | zh_SG.gb2312 | zh_SG.gb2312
 UTF8 | zh_SG.utf8 | zh_SG.utf8 | zh_SG.utf8
 EUC_TW | zh_TW | zh_TW.euctw | zh_TW.euctw
 UTF8 | zh_TW | zh_TW.utf8 | zh_TW.utf8
 EUC_TW | zh_TW.euctw | zh_TW.euctw | zh_TW.euctw
 UTF8 | zh_TW.utf8 | zh_TW.utf8 | zh_TW.utf8
 UTF8 | zu_ZA | zu_ZA.utf8 | zu_ZA.utf8
 LATIN1 | zu_ZA | zu_ZA | zu_ZA
 LATIN1 | zu_ZA.iso88591 | zu_ZA.iso88591 | zu_ZA.iso88591
 UTF8 | zu_ZA.utf8 | zu_ZA.utf8 | zu_ZA.utf8
(869行) 

ロケールでのデータベースの照合順序の設定

  • ロケールでデータベースのフィールドを構成する

    前提条件

    データベースの文字セットでサポートされている照合順序をよく理解してください。 次に、次のSQL文を実行して、データベースのエンコード形式を照会します。

    pg_databaseからのエンコーディングとしてdatname、pg_encoding_to_char(encoding) を選択します。

    サンプル出力

    datname | エンコーディング
    -------------------- -----------
     template1 | UTF8
     template0 | UTF8
     db | SQL_ASCII
     db1 | EUC_CN
     contribut_regression | UTF8
     test01 | UTF8
     test02 | UTF8
     postgres | UTF8
    (8行) 

    手順

    1. テーブルを作成するときは、次のSQL文を実行して、データベースの文字セットでサポートされている照合順序を指定します。

      CREATE TABLE test1 (
       テキストCOLLATE "de_DE" 、
       bテキストCOLLATE "es_ES"
      ); 
    2. 次のSQL文を実行して、列の照合順序を変更します。

      説明
      • テーブル内の列の照合順序を変更すると、テーブルが書き換えられます。 テーブルが大きい場合は注意してください。

      • 次の例では、test1はテーブルの名前を示し、aは照合順序の設定を変更する列の名前を示します。

      alter table test1 alter a type text COLLATE "en_US";
  • ロケール設定を構成します。

    • order by句で指定したソート順序を変更します。 例:

      select * from <table_name> order by <column_name> collate "C";
    • 演算子から返される結果を変更します。 例:

      サンプル文

      select * from <table_name> where <column_name> > 'Tom' collate "C";
  • ロケールインデックスを使用してデータを並べ替えます。

    インデックスを使用してデータを並べ替えることができるのは、ORDER by句で指定した照合順序がインデックスの照合順序と同じ場合だけです。 例:

    <table_name>(<column_name> collate "en_US") にインデックスidxaを作成します。explain select * from <table_name> order by <column_name> collate "en_US"; 

    サンプル出力:

    クエリ計画
    ------------------------------------------------------------------------
     インデックスのみ <table_name> でidxaを使用したスキャン (コスト=0.15 .. 31.55行=1360幅=64)
    (1行) 

結果をアルファベット順にソートするルールを設定する

次の4つの方法のいずれかを使用して、結果をアルファベット順にソートするために使用するルールを設定できます。

  • ロケールでサポートされているSQL文を使用します。 この方法では、元のデータを変更する必要はありません。 例:

    select * from <table_name> order by <column_name> collate "en_US";
  • ロケールでサポートされているフィールドを使用します。 データベースにデータが含まれている場合、この方法では元のデータを変更する必要があります。 例:

    alter table <table_name> alter <column_name> タイプテキストCOLLATE "en_US";
  • ロケールでサポートされているインデックスとSQL文を使用します。 この方法では、元のデータを変更する必要はありません。 例:

    <table_name>(<column_name> collate "en_US") にインデックスidxaを作成します。explain select * from <table_name> order by <column_name> collate "en_US"; 

    サンプル出力:

    クエリ計画
      ------------------------------------------------------------------------
       インデックスのみ <table_name> でidxaを使用したスキャン (コスト=0.15 .. 31.55行=1360幅=64)
      (1行) 
  • データベースの照合順序をen_USに設定します。 既定では、このデータベースのデータは、指定された照合順序に基づいてアルファベット順に並べ替えられます。 例:

    、データベースtest03エンコード 'UTF8' を作成します。lc_collate 'en_US.utf8' lc_ctype 'en_US.utf8' テンプレートtemplate0;
    \cテスト03
    select * from (values ('Alice'),('Tom')) as a(c1) order by c1 ; 

    サンプル出力:

    c1
      --------
       Alice
       Tom
      (2行) 
    説明

    漢字は複数の発音を有することができる。 例えば、中国の重慶市は、中慶市として符号化されてもよい。 発音に基づいて漢字をソートするために使用される照合順序を設定する場合は、注意してください。

Greenplumを使用して結果をアルファベット順にソートするルールを設定する

Greenplumでは、個々の列の照合順序を指定することはできません。 したがって、アルファベット順の結果のソートは、Greenplumでは異なります。

Greenplumを使用して、文字セット間で結果を変換できます。 次に、結果をバイナリ順にソートできます。 これにより、アルファベット順の結果に似た同様の結果を得ることができます。 例:

select * from (values ('Alice'), ('Tom')) t(id) order by byteain(textout(convert(id,'UTF8','EUC_CN')));

サンプル出力:

id
---------
 Alice
 トム
(2行) 

参考資料

PostgreSQL 15ドキュメント-第23章。 ローカライズ