So schreiben Sie Abfragen in PostgreSQL ohne Berücksichtigung der Groß-/Kleinschreibung

Um Text unabhängig von Groß- und Kleinschreibung zu vergleichen, können Sie ILIKE für Mustersuchen verwenden, normalisierte Werte mit LOWER oder UPPER vergleichen oder eine CITEXT-Spalte nutzen, wenn Gleichheitsvergleiche ohne Beachtung der Groß- und Kleinschreibung für diese Spalte üblich sind. Welche Variante passt, hängt davon ab, ob Sie ein Muster benötigen, wie die Spalte abgefragt wird und welche Indizes sowie Gebietsschemaeinstellungen gelten.

Die folgenden Beispiele verwenden eine Tabelle animal mit den Spalten id, age und type. Eine ihrer Zeilen enthält den Wert Kitten in type. Die SQL-Beispiele dienen der Veranschaulichung und wurden nicht auf einem PostgreSQL-Server ausgeführt.

ILIKE für Mustersuchen ohne Beachtung der Groß- und Kleinschreibung verwenden

Für einen Gleichheitsvergleich ohne Beachtung der Groß- und Kleinschreibung schreiben Sie type ILIKE 'kitten'. Soll ein Muster auch innerhalb eines Werts vorkommen können, verwenden Sie Platzhalter, zum Beispiel type ILIKE '%kitten%'. % steht für eine beliebige Zeichenfolge, _ für genau ein Zeichen. ILIKE ist das Gegenstück zu LIKE in PostgreSQL, das Muster ohne Beachtung der Groß- und Kleinschreibung vergleicht; sein Verhalten richtet sich nach dem aktiven Gebietsschema.

LIKE unterscheidet sich im üblichen Gebrauch nach Groß- und Kleinschreibung. Daher findet type LIKE 'kitten' den Wert Kitten unter einer darauf empfindlichen Kollation nicht. SIMILAR TO verwendet eine SQL-Mustersyntax, die regulären Ausdrücken ähnelt, während ILIKE die einfachere Platzhaltersyntax von LIKE nutzt. Die Operatoren decken unterschiedliche Suchanforderungen ab und sind nicht in jeder Abfrage austauschbar. PostgreSQLs Operator ~~* ist eine alternative Schreibweise für ILIKE.

Verwenden Sie ein exaktes Muster, wenn Sie einen Vergleich ähnlich einem Gleichheitstest möchten. Ergänzen Sie % oder _ nur, wenn die Suche ein breiteres Muster finden soll.

Beide Seiten eines Vergleichs mit LOWER oder UPPER normalisieren

Für einen Gleichheitsvergleich ohne Beachtung der Groß- und Kleinschreibung wenden Sie dieselbe Umwandlung auf die Spalte und den Suchwert an: LOWER(type) = LOWER('kitten'). Alternativ können Sie auf beiden Seiten UPPER verwenden. LOWER ist eine Funktion, die Text entsprechend dem Datenbankgebietsschema umwandelt. INITCAP schreibt Wörter mit Großbuchstaben am Wortanfang und ist kein allgemeiner Ersatz für einen Vergleich ohne Beachtung der Groß- und Kleinschreibung.

Die Umwandlung der Schreibweise und Vergleiche können vom Gebietsschema und der Kollation abhängen, insbesondere bei sprachspezifischen Zeichen. Gehen Sie weder davon aus, dass Textumwandlungen nur für Englisch funktionieren, noch dass jedes Gebietsschema alle Zeichen gleich behandelt. Wählen und testen Sie ein Datenbankgebietsschema und eine Kollation, die zu Ihren Daten und Vergleichsregeln passen.

Indizes für Suchen ohne Beachtung der Groß- und Kleinschreibung hinzufügen

Ein gewöhnlicher Index auf type unterstützt eine Bedingung für LOWER(type) möglicherweise nicht so wie ein Index mit demselben Ausdruck. Erstellen Sie für Gleichheitsabfragen mit normalisierten Werten einen Ausdrucksindex, der dem Ausdruck in der Abfrage entspricht, zum Beispiel CREATE INDEX animal_type_lower_idx ON animal (LOWER(type));. Ein entsprechender Index auf UPPER(type) kann eine Abfrage unterstützen, die UPPER(type) verwendet.

Ein Index ist nur dann nützlich, wenn sein Ausdruck und Operator die Abfrage unterstützen und der Planer ihn auswählt; ein schnelleres Ergebnis ist damit nicht garantiert. PostgreSQLs Erweiterung pg_trgm kann GIN- oder GiST-Trigrammindizes für unterstützte LIKE- und ILIKE-Suchen bereitstellen. Ob das hilft, hängt vom Muster, der Tabelle und der Arbeitslast ab. Prüfen Sie Abfragepläne und Laufzeiten mit repräsentativen Daten, bevor Sie einen Index auswählen.

Wenn Werte, die sich nur durch Groß- und Kleinschreibung unterscheiden, als Duplikate abgelehnt werden sollen, kann ein eindeutiger Ausdrucksindex wie CREATE UNIQUE INDEX animal_type_lower_unique ON animal (LOWER(type)); diese Regel für die vom Ausdruck erfassten Werte durchsetzen. Das ist eine Entscheidung für das Schema: Prüfen Sie vor der Umsetzung, ob sie den beabsichtigten Datenregeln entspricht.

CITEXT für Spalten ohne Beachtung der Groß- und Kleinschreibung verwenden

Die Erweiterung CITEXT stellt einen Texttyp bereit, dessen Vergleiche die Groß- und Kleinschreibung nicht beachten. So kann ein gewöhnlicher Gleichheitsvergleich Werte finden, auch wenn sie unterschiedlich geschrieben sind. Aktivieren Sie die Erweiterung mit CREATE EXTENSION citext; und deklarieren Sie die Spalte anschließend als CITEXT, zum Beispiel type CITEXT. Eine Abfrage wie SELECT id, age, type FROM animal WHERE type = 'kitten'; kann dann einen gespeicherten Wert Kitten finden, entsprechend dem Vergleichsverhalten dieses Typs.

CITEXT ist praktisch, wenn Vergleiche ohne Beachtung der Groß- und Kleinschreibung für eine Spalte der Standard sein sollen. Der Ansatz ist nicht automatisch schneller oder kostengünstiger als andere Möglichkeiten; Indizes, Kollation und Arbeitslast müssen weiterhin berücksichtigt werden. Die Erweiterung muss in der Datenbank verfügbar und aktiviert sein. Prüfen Sie vor der Verwendung das dokumentierte Vergleichsverhalten für Ihr Gebietsschema und Ihre Daten.

Als portable Veranschaulichung der Normalisierung vergleicht das folgende Python-Beispiel zwei Schreibweisen mit ASCII-Zeichen. Es zeigt ausschließlich das Verhalten von Python-Zeichenfolgen: Es führt weder PostgreSQL-SQL aus noch belegt es ein bestimmtes Datenbank-Kollationsverhalten.

stored_type = "Kitten"
search_type = "kitten"
print(stored_type.lower() == search_type.lower())

True