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行)
手順
テーブルを作成するときは、次のSQL文を実行して、データベースの文字セットでサポートされている照合順序を指定します。
CREATE TABLE test1 ( テキストCOLLATE "de_DE" 、 bテキストCOLLATE "es_ES" );
次の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行)