無料スクリプト配布のPHP.TO   PHPの実用的なtips PHPマニュアル MySQLマニュアル Apacheマニュアル PostgreSQLマニュアル マニュアル検索    

F.7. citext

citext モジュールは、大文字小文字の区別がない文字列型を提供します。 これは値の比較の際、基本的に内部的で lower を呼び出します。 この他はほぼ text と同様に動作します。

F.7.1. 論拠

PostgreSQL において大文字小文字の区別のない比較を行う標準的な手法は、値を比べる際に以下のように lower 関数を使用することでした。例です。

SELECT * FROM tab WHERE lower(col) = LOWER(?);

これはまあまあ動作しますが、数多くの欠点があります。

  • 作成するSQL文を冗長にします。 また常に列と問い合わせの値両方に lower を使用することを忘れないようにしなければなりません。

  • lower を使用して関数インデックスを作成していない限り、インデックスを使用しません。

  • UNIQUE または PRIMARY KEY として列を宣言するのであれば、暗黙的に生成されるインデックスは大文字小文字を区別します。 このため、大文字小文字を区別しない検索では使えず、また、大文字小文字を区別しない一意性を強制させられません。

citext データ型によりSQL問い合わせ内の lower 呼び出しを省くことができます。 さらに、大文字小文字の区別がないプライマリキーを実現できます。 citext text と同様にロケールも考慮します。 つまり大文字と小文字のマッチングは、 LC_CTYPE データベース設定の規則に依存します。 繰り返しますが、この動作は lower を使用した問い合わせと同一です。 しかしこのデータ型により、ロケールの考慮は透過的に行われますので、問い合わせで特殊なことを行うことを覚えておく必要はありません。

F.7.2. 使用法

簡単な例を示します。

CREATE TABLE users (
    nick CITEXT PRIMARY KEY,
    pass TEXT   NOT NULL
);

INSERT INTO users VALUES ( 'larry',  md5(random()::text) );
INSERT INTO users VALUES ( 'Tom',    md5(random()::text) );
INSERT INTO users VALUES ( 'Damian', md5(random()::text) );
INSERT INTO users VALUES ( 'NEAL',   md5(random()::text) );
INSERT INTO users VALUES ( 'Bjørn',  md5(random()::text) );

SELECT * FROM users WHERE nick = 'Larry';

SELECT 文は、 nick 列が larry に設定され、問い合わせが Larry に対してであっても、1つのタプルを返します。

F.7.3. 文字列比較の動作

citext はそれぞれの文字列を( lower が呼ばれますが)小文字に変換して結果を普通に比較します。 よって、例えば lower で小文字にした場合に同じ結果となるような2つの文字列が等しいとみなされます。

大文字小文字の区別のない照合をできる限り正確にエミュレートするために、数多くの citext 独自版の各種文字列処理演算子と関数があります。 したがって、例えば正規表現演算子 ~ および ~* は、 citext に適用する時に同じ動作を提供します。 これら両方は大文字小文字を区別することなくマッチさせます。 !~ !~* だけではなく LIKE 演算子、 ~~ ~~* !~~ !~~* でも同じことが言えます。 もし大文字小文字を区別して比較したい場合は、演算子の引数を text にキャストすることができます。

引数が citext であれば、同様にして以下の関数は大文字小文字を区別しない一致を実行します。

  • regexp_replace()

  • regexp_split_to_array()

  • regexp_split_to_table()

  • replace()

  • split_part()

  • strpos()

  • translate()

正規表現関数(RegExp関数)では、大文字小文字を区別して一致させたい場合 "c" フラグを付けて、強制的に大文字小文字を区別して一致させることができます。 そうしないと、大文字小文字を区別させたい場合にはこれらの関数のいずれかを使用する前段階で text にキャストしなければなりません。

F.7.4. 制限

  • citext の大文字小文字を区別しない動作は使用するデータベースの LC_CTYPE に依存します。 どのように値を比較するかは、データベースが作成されたときに決定されます。 Unicode標準の定義という観点では、真に大文字小文字の区別がないわけではありません。 実質的に何を意味しているかというと、使用している照合が十分なものであれば、 citext による比較も十分なものになるはずです。 しかしデータベースに様々な言語でデータを格納している場合は、ある言語のユーザは照合が他の言語用のものであった場合想定外の問い合わせ結果を得るかもしれません。

  • PostgreSQL 9.1では、 COLLATE 指定を citext 列もしくはデータ値に付け加えることができます。 現状では、 citext 演算子は大文字小文字を含んだ文字列を比較する際に、デフォルトではない COLLATE 指定を重んじます。 しかし、最初の小文字変換はデータベースの LC_CTYPE 設定にしたがって、常に実行されます(つまり、 COLLATE "default" が指定されたようになります) これは、両方のステップが入力された COLLATE 指定に従うように、将来のリリースにおいて変更されるでしょう。

  • 演算子関数およびB-Tree比較関数でデータの複製を作成しそれを比較のために小文字に変換しなければなりませんので、 citext text ほど効率的ではありません。 しかし大文字小文字の区別をしない一致をさせるために lower を使用する場合よりかなり効率的です。

  • citext は、ある文脈では大文字小文字の区別を行い、またある文脈では大文字小文字の区別を行わない比較をする必要がある場合、あまり役に立ちません。 標準的な解法は text 型を使用し、大文字小文字を区別する比較が必要であれば手作業で lower 関数を使用することです。 これは大文字小文字を区別しない比較の必要性がまれであれば、問題なく動作します。 大文字小文字を区別しない比較がほとんどで、大文字小文字を区別する比較の必要性がまれである場合は、データを citext として格納し、大文字小文字を区別する比較の際にその列を明示的に text にキャストすることを検討してください。 どちらの場合でも、2種類の検索の両方を高速にするために2つのインデックスを作成しなければならないでしょう。

  • citext 演算子を含んだスキーマは、現在の search_path (典型的には public )に 存在しなければいけません。もし無い場合は通常の大文字小文字が区別される text 比較が代わりに呼び出されます。

F.7.5. 作者

David E. Wheeler

Donald Fraserによる citext モジュール原本からのヒント


powered by SEO.CUG.NET