PostgreSQL で大文字と小文字を区別しないクエリを作成する

1. PostgreSQL で PATTERN MATCHING を使用する
2. LOWER 関数を使用して、PostgreSQL で検索中にケースを類似させる
3. PostgreSQL での CASE CONVERSION の使用
4. CITEXT を使用してテーブルを作成し、PostgreSQL で CASE-INSENSITIVE マッチングを実行する

DBMS でクエリを作成しているときに、句または構文を満たすために必要な条件に応じて、一部の文字を大文字または小文字にする必要があることに気付くことがよくあります。 その結果、状況に関係なく、クエリを完了し、一般的な構文エラーを回避する方法を見つける必要があります。

この記事では、PostgreSQL で大文字と小文字を区別しないクエリを作成する方法について説明します。

PostgreSQL で PATTERN MATCHING を使用する

PostgreSQL でクエリを最適化する方法の 1つで、データベースで何かを見つけようとしていて、含まれているオブジェクトと一致する特定の文字列を提供する必要がある場合、そのために使用したのと同じ文字ケースを使用する必要がある場合があります。 検索中のオブジェクト。

これは、以前に定義されたときにオブジェクト名の正確な構文を忘れた場合に問題を引き起こす可能性があります。

ここでは、PATTERN MATCHING のような単純なものを使用できます。 ID、AGE および TYPE を持つテーブル ANIMAL があります。

テーブルには、次のようなデータが既に含まれています。

       ID     AGE   TYPE
1     2 12   "Horse"
2     1 3    "Cat"
3     3 4    "Kitten"

以下のクエリを使用して、タイプ KITTEN のすべての ANIMALS を見つけることができます。

クエリ:

select * from ANIMAL where type = 'Kitten';

出力：

ただし、次のクエリを記述した場合:

select * from ANIMAL where type = 'kitten';

その場合、結果に何も得られません。これは、文字 'k' が 'K' と等しくないために発生します。 これを解決するには、ILIKE 演算子を使用して大文字と小文字を区別しないようにします。

クエリ:

select * from ANIMAL where type ILIKE 'kitten';

このクエリは、KITTEN タイプのすべての動物を返します。 このコードへの変更は、ILIKE の代わりに使用できる符号演算子にすることができます。

クエリ:

select * from ANIMAL where type ~~* 'kitten';

使用できるもう 1つの方法は、LIKE または SIMILAR TO 演算子ですが、名前の一部を正しい大文字と小文字で覚えている場合にのみ使用できます。

たとえば、KITTEN を検索するには、私たちの名前の ITTEN が小さく、K が SMALL または BIG のいずれかであったことを覚えている場合、次のように記述して結果を照会できます。

クエリ:

select * from ANIMAL where type similar to '%itten';

これは良い代替手段ではありません。ユーザーがオブジェクトの作成時に使用される命名規則について何らかの考えを持っている場合に使用する必要があります。 ILIKE は、提供された文字列とパターンを一致させるときに、大文字と小文字を区別する処理を改善します。

LOWER 関数を使用して、PostgreSQL で検索中にケースを類似させる

パターンと文字列を一致させる非常に効率的な方法は、両方の文字を類似させることです。 すべての文字を LOWER または UPPER に変換してから、それぞれを一致させます。

PostgreSQL には、チェック用の関数 LOWER() と UPPER() が用意されています。

クエリ:

select * from ANIMAL where lower(type) = lower('kitten')

また

select * from ANIMAL where lower(type) = lower('kitten')

出力：

同様に、INITCAP を使用して、パターンと文字列の開始文字だけを大文字にし、残りを小文字にして、照合とチェックを行うことができます。

クエリ:

select * from ANIMAL where initcap(type) = initcap('kitten')

PostgreSQL での CASE CONVERSION の使用

PostgreSQL のINDEXES ON EXPRESSIONSは、大きなテーブルからのクエリ結果を高速化します。 テーブルに対して同じクエリを繰り返し呼び出して時間がかかる代わりに、テーブルにインデックスを付けてクエリ時に使用できます。

CASE CONVERSIONS は既存の INDEXES を無効にする可能性があることに注意してください。 次のクエリを記述して、この CASE MATCHING のインデックスを作成できます。

クエリ:

create index lower_col on ANIMAL (lower(type));

また

create index upper_col on ANIMAL (upper(type));

同様に、INITCAP についても同様です。 これらの INDEXES を使用して、ROW INSERTION の制約を設定することもできます。別の CASE を持つデータセットが INSERTED の場合、複製時に無効にすることができます。

LIKE および ILIKE クエリを高速化するには、PG_TRGM_EXTENSION で GIN または GIST インデックスを使用できます。

CITEXT を使用してテーブルを作成し、PostgreSQL で CASE-INSENSITIVE マッチングを実行する

PostgreSQL での一般的なマッチングの別の代替手段は、CITEXT 句を使用することです。 ユーザーが文字列をパターンに一致させようとするたびに LOWER() を置くのではなく、値を比較するときに LOWER() を内部的に呼び出します。

列 TYPE を CITEXT として表 ANIMAL を作成しましょう。

create extension CITEXT; --creating the extension first

create table ANIMAL(
    id INT PRIMARY KEY,
    age INT,
    TYPE CITEXT
)

次に、前の表と同じ値を INSERT します。 ここで、次のクエリを使用して結果を返します。

select * from ANIMAL where type = 'kitten';

コラムで CITEXT を使用すると、上記のソリューションの多くよりも安価で、効率的で、高速であることがわかります。 CITEXT はデータベースの LC_CTYPE 設定に依存し、必要に応じて変更できます。

マッチングの前に、CASE CONVERSIONS についていくつかの重要なポイントがあります。

1. ケースを別の言語に変換できません (英語を除く)。
2. LOWER() および UPPER() 関数は、インデックスがないため遅くなります。

これらはすべて、PostgreSQL で文字列をパターンと比較するさまざまな方法です。